Update/Append Queries

R

rdw1260

I'm using MS Access 2003. I want to update our membership database using
either the CSV or the DIF file provided by our national organization. My
problem is that both of the files have where appropriate, the leading zero's
are not showing for SSN and EIN fields. For example, for a member who's SSN
has 2 leading zero's, the member's SSN shows 7 digits instead of 9.
Similarly, a member who's SSN has 1 leading zero, the member's SSN shows 8
digits instead of 9. My Access table has 9 digits for the SSN and the EIN
field.

How should I handle this issue?

Thanks in advance for any responses.
 
D

Douglas J Steele

Make the fields Text, not Numeric. While they may be numbers, you're not
doing calculations with them, so a Numeric field isn't necessary.

If you're saying that the incoming data is incorrect, you can write an
Update query to correct the data once it's been imported.

To add as many preceding zeroes as are required, the query would look like:

UPDATE MyTable SET MyField = Right$("000000000" & [MyField], 9)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"donotspam"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top