Char(n) SQL data type equivalent in Microsoft Access?

Question

New to access here. I need to have a field in a table of integer value maximum length 2 characters. I was hoping Access would prefix a 0 (zero) to the attribute when it only was comprised of one character. Any suggestions??
For example, I enter the value 2 in a field, and I would like it to be store as 02 to facilitate concatenation with another field.
Kind regards, and many thanks.

Anonymous · Accepted Answer

An integer doesn't have leading zeros (that's math, not Access).  If you want a 2 character text field, you'll have to format the data with a leading zero in your insert code.

Right$("0" & <your variable with the text>, 2) will give you the rightmost 2 characters of 0 plus your input.  So 1 will be 01, 10 will be 10.  (100 will be 00, so you'll have to make an additional test to make sure the user isn't entering bad data.)

TheMadProfessor · Answer

If you know for sure that your values will be limited to 0 thru 100, I'd suggest using a single character in the database and employ the Byte data type in computations. A check constraint could guarantee that you don't fall out of range when adding/changing values and zero-padding can be handled at the appropriate time programatically rather than trying to store them physically.

? · Answer

hi, this is undemanding to do; you do no longer ought to have your consumer go into the code (that must be extremely grotesque!) What you ought to do is create a parametrized question on your VBA code. i.e. you will possibly carry jointly your sq. string every time on the fly, making use of a catalogue of attributes chosen in a style with comboboxes/textboxes/checkboxes, and so on.

Char(n) SQL data type equivalent in Microsoft Access?

3 Answers