Update/Append Queries

[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.

 

[SusanV]

Hi rdw,

What is the field type?
SusanV


"(e-mail address removed).(donotspam)"

 

[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"

 

[rdw1260]

The field type in the table is text.
--
Bob

Hi rdw,

What is the field type?
SusanV


"(e-mail address removed).(donotspam)"