Query to text file cuts my leading zeros off.

  • Thread starter Thread starter Vadimbar
  • Start date Start date
V

Vadimbar

Hello, this is my third attempt to post this question.
I have a query that has a value of "012810" in one of the fields.

When I run the query it shows up as 012810.
When I export to excell it shows up as 012810.
When I export to text file it always cuts off the zero and I get 12810.

If I change the value to say 212810 and then export to a text file it works
fine and returns 212810.

Why is the zero being chopped off during a text file export?

Thank you,
VADIMBAR
 
Its being treated as a numeric value.
Is this column defined as numeric or text?
It needs to be defined as text.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Vadimbar said:
Hello, this is my third attempt to post this question.
I have a query that has a value of "012810" in one of the fields.

When I run the query it shows up as 012810.
When I export to excell it shows up as 012810.
When I export to text file it always cuts off the zero and I get 12810.

If I change the value to say 212810 and then export to a text file it works
fine and returns 212810.

Why is the zero being chopped off during a text file export?
Click to expand...


That has to do with setting the number's format in the query
field and worksheet cell. In this case, change the
exporting query's field to use the Format function:
field: Format(table.field, "000000")

OTOH, if it's a Text field in its table, it's somehow being
converted to a number in the export process. You should
track down the reason why it's being converted and fix it.
If all else fails, you can bail out by using the Format
function as above.
 
Dorian said:
Its being treated as a numeric value.
Is this column defined as numeric or text?
It needs to be defined as text.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
Click to expand...
 
I finallly got it to work!!! Thank you both for your suggestions.

I used Format(Date(),"mmddyy") instead of the forcing the value manually
Trans Date:"012810"
Still acted wierd. Then I craeted the same in a different field and it work
fine. It was something with the export wizard that forced to accept the first
time as a numeric and I just could not locate a way to change it. Once I
deleted it and re-created it worked.

Thank you,
Vadimbar
 
Back
Top