Query to text file cuts my leading zeros off.

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
 
D

Dorian

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".
 
M

Marshall Barton

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?


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

Vadimbar

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".
 
V

Vadimbar

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
 

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