preserve leading zeros in txt file

[pungentSapling]

I need to preserve leading zeros in a field when I export a table to txt.
The "hours" field needs to contain 5 digits for example 40 hours should look like 04000. I am using a query for the export, the dynaset for the query contains the right amount of zeros but when the export is performed I lose the leading zeros.
I searched the forum and found what I thought was the fix

Select Format(Date,"00000") as Date1

but I can not seem to get it to work.

any help would be greatly appreciated.




I know I have (sort of) posted this question twice, the other time I accidently used Quote and it ended up appending my question to the thread I was trying to quote from......Sorry

 

[pungentSapling]

Should I convert the field to a string rather than a number?
.txt files don't know the difference between numbers and strings...right?

 

[Howlsta]

Yeah i had a problem like this except the other way around. I was trying to remove the leading zero's.

That's right you should convert to string.

Rich

 

[pungentSapling]

thanks! But now another question..
what is s the best way to convert the field to string. I tried making another query based on the old query that used@@@@@ as the "hours" field format but the dynaset for the new query is dropping the leading zeros

 

[David R]

CStr() is your friend

Or Str() under certain cases. Look it up in Access help.

 

[The_Doc_Man]

Queries are your friends here.

Make a query that lists every field in the table you want to export.

Delete any fields from the query if you didn't want to see them in the export.

Apply the Format function in the query.

Export the query.

 

[pungentSapling]

thanks, I finally got it to work....had to use another query with the formatting thanks for all the help.