.csv link, field format

[lndsy]

I have a link table created that links to a .csv file.

I can't get the SS#'s in one of the fields of the link table to show the first number of the SS#'s if the first number is 0.

It is important that the 0's are there, as I have several update queries that depend on this format.

any ideas?

 

[BNPMike]

The only way you need the leading zero is if you are using the number as a string because leading zeros are always supressed, so if even if you managed to bring across such a number you couldn't preserve its format. You therefore need to make the field a string. Probably the easiest way is to wrap all your access to the file in a query that changes the data to a string and pad out the front with zeros.
That's the sort of thing queries are for.

 

[Blorf]

An alternative is to use a function to fix it after you bring the data in. Like right ("000000000" & [SS#], 9)

ChaZ

Ascii dumb question, get a dumb Ansi

 

[Craig0201]

SSNs are numbers merely in name. I can't see how you would ever want to perform calculations on it so store it as a string. This pretty much holds true for any field. If you ain't calculating with it, store it as a string.

You can set this in the import specification being used.

Craig

 

[lndsy]

Thank you everyone!!! I really appreciate your help and input.

The .csv file that I have linked to doesn't have the leading numbers on the SS#'s, so I guess this is why making the link table field a string did not help.

I did, however, create a new query with all of the fields from the link table and replacing the SS# field with Expr1: Format([SSN],'000000000') to make the 0's show. I am able to use this query in the update queries now.

Blorf??
Are you saying that I can use a custom format for the field in the link table itself for the SS# field? If so, the function you provided did not work, but I would be extremely interested in getting the right one, so that I could skip the query of the table step. Thank you so much for helping me

 

[Blorf]

No. My suggestion was to use a query like you described.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[lndsy]

Ok ...thanks Chaz!