How Do I Export Integer Data And Maintain Leading Zeros?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've just created my 1st access database. I have several integer fields that
need to maintain leading zeros when exported to a CSV text file. For
example: I have created these fields as integers with a format of "00000000".
When I look in the Access tables, I will see a value of "00002745" which is
exactly what I need. The problem is that I cannot get them to export to a
CSV text file with the leading zeros. I've tried the export wizard, but I
cannot get that to work. I'm trying to use a "schema.ini" file, but I cannot
get the following line of code to work.

DoCmd.TransferText acExportDelim, "c:\schema.ini", "MyQuery_qry",
"c:\Test.txt"

The error I get is... "The text file specification 'c:schema.ini' does not
exist". It's seems like it's having a problem with the back slash because it
never comes back in the error message. If I omit the specification
parameter, I get the same error on the text file. any ideas??? Is there an
easier approach to this issue?
 
First, you can't carry integers in an Access database formatted as text.
Either you are looking at a formatted view of the field or it is not really
an integer field, but a text field.
If you need to see leading zeros on an integer field, carry them in your
table as an integer field, and use the Format function whenever you want to
present them to a human. That would include, forms, report, and, in this
case, how you present it in your query.
Rather than putting the name of the field to export in the field name row of
the query builder, make it a calculated field. That is, give it a name
followed by a colon and the value or format you want it to be. For example:

SomeName: Format([SomeNumber], "00000000")
 
Back
Top