How to get the prefix zero to show in account?

[AccessNewby]

Hello,

I'm very green with Access so please be patient with me. In my table, the Account column should be a 9 digit number. However, when I download the data from another application if any account has a prefix zero, it drops off, left me with 8 digit. When I try to match the account in two tables, I do not get a match for any 8 to the 9 digit account. In Excel, I can use the =Text(cell number, "000000000") but I don't know what to do in Access. Do I need to do a query or can this be done as as an Input Mask?

Thank you.

 

[Alansidman]

What is the property setting in your table for the field. Is it text? Probably not. Change your property setting for the field to text. Be sure when you import that you have the property as text and it will import the number as text. this will preserve preceding zeros. Is this a SSN or TIN that you are working with?

 

[AccessNewby]

Thank you for your reply. The setting is on Text and the Account is a 9-digit number. It's not a SSN or TIN (I don't know what TIN is).

Thank you,
AccessNewby

 

[gemma-the-husky]

its always a problem. in access, if you want to see leading zeroes you really need to treat the column as text

so it depends how you are downloading the data - whether from csv or form excel - if excel then access may well treat the columns as numeric even though you have them formatted as text. if csv then you may have more control, but the leading zero may not be in the csv file

PERSONALLY, i would not try to use such a field as a link - i would add an autonumber to one table, and use that as the foreign key - but this may be too late now

 

[AccessNewby]

Thank you all.

 

[Atomic Shrimp]

If you're importing a table from Excel, Access will guess the field type based on the content of the first few rows, and may not give you the opportunity to override its choices.

You can circumvent this behaviour by opening the excel sheet and inserting a dummy first record - insert a bit of proper text ("Blah", for example) in all the fields you definitely want to import as text, then delete the dummy record after you've imported it into Access.

 

[raskew]

Hi -

In addition to what's already been provided, you might want to remember that 'real' numbers do not have leading zeroes.

Bob