TransferText with leading 0's

[Randy K.]

I have a query that splits a numeric field in to several parts including the
last 5 characters and export the query to a text file (using an export spec
that defines a ~ delimiter). While the query displays all 5 digits just
fine even if they start with leading 0's, the resulting file drops all the
0's and the field is the wrong length. This file is used by a labeling
program and I must have all 5 digits, 0's or not.

Any ideas? I have tried cstr(right(field,5)) and the query always look
fine, it is just the transfertext function that messes it up.

TIA,
Randy K.

 

[John Nurick]

Hi Randy,

Try something like this in the query:
Format([NumericField], "00000")
or this:
Right("00000" & [TextField], 5)

 

[Randy K.]

Both show fine in the query (as did just right,5) but doesn't transfer.
Randy

Hi Randy,

Try something like this in the query:
Format([NumericField], "00000")
or this:
Right("00000" & [TextField], 5)

I have a query that splits a numeric field in to several parts including the
last 5 characters and export the query to a text file (using an export spec
that defines a ~ delimiter). While the query displays all 5 digits just
fine even if they start with leading 0's, the resulting file drops all the
0's and the field is the wrong length. This file is used by a labeling
program and I must have all 5 digits, 0's or not.

Any ideas? I have tried cstr(right(field,5)) and the query always look
fine, it is just the transfertext function that messes it up.

TIA,
Randy K.

 

[John Nurick]

It works fine for me. Here's the text of the query I used:

SELECT AddrNew.ID, Format([AddrNew].[ID],"00000") AS fID,
AddrNew.Firstname, AddrNew.Lastname
FROM AddrNew;

and here's a bit of the output file:

ID~fID~Firstname~Lastname
5~00005~Amanda~Anderson
18~00018~Amanda~Pogle
4~00004~Erica~Doe

When you say "doesn't transfer", do you mean that the leading zeros
aren't there when you open the text file in Notepad, or that they don't
appear when you import the data into another application?

If the former, try exporting the query manually. Don't use the export
specification, just select the ~ delimiter. If that works, there's
probably something wrong with the export spec.

If the file is OK in Notepad, the problem is with the other application.

Both show fine in the query (as did just right,5) but doesn't transfer.
Randy

Hi Randy,

Try something like this in the query:
Format([NumericField], "00000")
or this:
Right("00000" & [TextField], 5)

I have a query that splits a numeric field in to several parts including the
last 5 characters and export the query to a text file (using an export spec
that defines a ~ delimiter). While the query displays all 5 digits just
fine even if they start with leading 0's, the resulting file drops all the
0's and the field is the wrong length. This file is used by a labeling
program and I must have all 5 digits, 0's or not.

Any ideas? I have tried cstr(right(field,5)) and the query always look
fine, it is just the transfertext function that messes it up.

TIA,
Randy K.

 

[Randy K.]

My query would like like SELECT field1, format(right(field2,5),"00000", some
other fields. (field2 is a int either 7 or 9 digits long,{the year + 5
digits, changing to YYYY in 2000}) and all of this data is queried from an
linked table via ODBC if that makes a difference.

What I see in my query is: (with or without using the format function)
2004,00001,blah blah blah
2004,00002, blah blah blah

What ends up in my .txt file is:
2004~1~blah blah blah
2004~2~blah blah blah

Randy

It works fine for me. Here's the text of the query I used:

SELECT AddrNew.ID, Format([AddrNew].[ID],"00000") AS fID,
AddrNew.Firstname, AddrNew.Lastname
FROM AddrNew;

and here's a bit of the output file:

ID~fID~Firstname~Lastname
5~00005~Amanda~Anderson
18~00018~Amanda~Pogle
4~00004~Erica~Doe

When you say "doesn't transfer", do you mean that the leading zeros
aren't there when you open the text file in Notepad, or that they don't
appear when you import the data into another application?

If the former, try exporting the query manually. Don't use the export
specification, just select the ~ delimiter. If that works, there's
probably something wrong with the export spec.

If the file is OK in Notepad, the problem is with the other application.

Both show fine in the query (as did just right,5) but doesn't transfer.
Randy

Hi Randy,

Try something like this in the query:
Format([NumericField], "00000")
or this:
Right("00000" & [TextField], 5)


On Tue, 6 Jan 2004 12:07:33 -0700, "Randy K."

I have a query that splits a numeric field in to several parts

including
the

last 5 characters and export the query to a text file (using an export spec
that defines a ~ delimiter). While the query displays all 5 digits just
fine even if they start with leading 0's, the resulting file drops all the
0's and the field is the wrong length. This file is used by a labeling
program and I must have all 5 digits, 0's or not.

Any ideas? I have tried cstr(right(field,5)) and the query always look
fine, it is just the transfertext function that messes it up.

TIA,
Randy K.

 

[John Nurick]

Randy,

Is field2 a text field or a number field?

My query would like like SELECT field1, format(right(field2,5),"00000", some
other fields. (field2 is a int either 7 or 9 digits long,{the year + 5
digits, changing to YYYY in 2000}) and all of this data is queried from an
linked table via ODBC if that makes a difference.

What I see in my query is: (with or without using the format function)
2004,00001,blah blah blah
2004,00002, blah blah blah

What ends up in my .txt file is:
2004~1~blah blah blah
2004~2~blah blah blah

Randy

It works fine for me. Here's the text of the query I used:

SELECT AddrNew.ID, Format([AddrNew].[ID],"00000") AS fID,
AddrNew.Firstname, AddrNew.Lastname
FROM AddrNew;

and here's a bit of the output file:

ID~fID~Firstname~Lastname
5~00005~Amanda~Anderson
18~00018~Amanda~Pogle
4~00004~Erica~Doe

When you say "doesn't transfer", do you mean that the leading zeros
aren't there when you open the text file in Notepad, or that they don't
appear when you import the data into another application?

If the former, try exporting the query manually. Don't use the export
specification, just select the ~ delimiter. If that works, there's
probably something wrong with the export spec.

If the file is OK in Notepad, the problem is with the other application.

Both show fine in the query (as did just right,5) but doesn't transfer.
Randy

Hi Randy,

Try something like this in the query:
Format([NumericField], "00000")
or this:
Right("00000" & [TextField], 5)


On Tue, 6 Jan 2004 12:07:33 -0700, "Randy K."

I have a query that splits a numeric field in to several parts including
the
last 5 characters and export the query to a text file (using an export
spec
that defines a ~ delimiter). While the query displays all 5 digits just
fine even if they start with leading 0's, the resulting file drops all
the
0's and the field is the wrong length. This file is used by a labeling
program and I must have all 5 digits, 0's or not.

Any ideas? I have tried cstr(right(field,5)) and the query always look
fine, it is just the transfertext function that messes it up.

TIA,
Randy K.

 

[Randy K.]

Long Integer (is a linked table)

Randy,

Is field2 a text field or a number field?

My query would like like SELECT field1, format(right(field2,5),"00000", some
other fields. (field2 is a int either 7 or 9 digits long,{the year + 5
digits, changing to YYYY in 2000}) and all of this data is queried from an
linked table via ODBC if that makes a difference.

What I see in my query is: (with or without using the format function)
2004,00001,blah blah blah
2004,00002, blah blah blah

What ends up in my .txt file is:
2004~1~blah blah blah
2004~2~blah blah blah

Randy

It works fine for me. Here's the text of the query I used:

SELECT AddrNew.ID, Format([AddrNew].[ID],"00000") AS fID,
AddrNew.Firstname, AddrNew.Lastname
FROM AddrNew;

and here's a bit of the output file:

ID~fID~Firstname~Lastname
5~00005~Amanda~Anderson
18~00018~Amanda~Pogle
4~00004~Erica~Doe

When you say "doesn't transfer", do you mean that the leading zeros
aren't there when you open the text file in Notepad, or that they don't
appear when you import the data into another application?

If the former, try exporting the query manually. Don't use the export
specification, just select the ~ delimiter. If that works, there's
probably something wrong with the export spec.

If the file is OK in Notepad, the problem is with the other application.

On Tue, 6 Jan 2004 15:42:43 -0700, "Randy K."

Both show fine in the query (as did just right,5) but doesn't transfer.
Randy

Hi Randy,

Try something like this in the query:
Format([NumericField], "00000")
or this:
Right("00000" & [TextField], 5)


On Tue, 6 Jan 2004 12:07:33 -0700, "Randy K."

I have a query that splits a numeric field in to several parts including
the
last 5 characters and export the query to a text file (using an export
spec
that defines a ~ delimiter). While the query displays all 5 digits just
fine even if they start with leading 0's, the resulting file drops all
the
0's and the field is the wrong length. This file is used by a labeling
program and I must have all 5 digits, 0's or not.

Any ideas? I have tried cstr(right(field,5)) and the query always look
fine, it is just the transfertext function that messes it up.

TIA,
Randy K.

 

[John Nurick]

If I've got it right, field2 contains numbers like

9800312
9900020
200065432
200300001
which you want to export to the text file as
00312
00020
65432
00001
respectively.

A good general rule is "Numerical operations on numbers, string
operations on strings". To get the last 5 digits of a decimal integer,
mod it by 100000. You can then use Format() to convert the result to a
string with leading zeros:
Format([Field2] mod 100000, "00000")
Alternatively, convert the 7- or 9- digit number to a 9-character string
and then return the last 5 characters:
Right(Format([Field2], "000000000"), 5)

Long Integer (is a linked table)

Randy,

Is field2 a text field or a number field?

My query would like like SELECT field1, format(right(field2,5),"00000", some
other fields. (field2 is a int either 7 or 9 digits long,{the year + 5
digits, changing to YYYY in 2000}) and all of this data is queried from an
linked table via ODBC if that makes a difference.

What I see in my query is: (with or without using the format function)
2004,00001,blah blah blah
2004,00002, blah blah blah

What ends up in my .txt file is:
2004~1~blah blah blah
2004~2~blah blah blah

Randy

It works fine for me. Here's the text of the query I used:

SELECT AddrNew.ID, Format([AddrNew].[ID],"00000") AS fID,
AddrNew.Firstname, AddrNew.Lastname
FROM AddrNew;

and here's a bit of the output file:

ID~fID~Firstname~Lastname
5~00005~Amanda~Anderson
18~00018~Amanda~Pogle
4~00004~Erica~Doe

When you say "doesn't transfer", do you mean that the leading zeros
aren't there when you open the text file in Notepad, or that they don't
appear when you import the data into another application?

If the former, try exporting the query manually. Don't use the export
specification, just select the ~ delimiter. If that works, there's
probably something wrong with the export spec.

If the file is OK in Notepad, the problem is with the other application.

On Tue, 6 Jan 2004 15:42:43 -0700, "Randy K."

Both show fine in the query (as did just right,5) but doesn't transfer.
Randy

Hi Randy,

Try something like this in the query:
Format([NumericField], "00000")
or this:
Right("00000" & [TextField], 5)


On Tue, 6 Jan 2004 12:07:33 -0700, "Randy K."

I have a query that splits a numeric field in to several parts
including
the
last 5 characters and export the query to a text file (using an export
spec
that defines a ~ delimiter). While the query displays all 5 digits
just
fine even if they start with leading 0's, the resulting file drops all
the
0's and the field is the wrong length. This file is used by a
labeling
program and I must have all 5 digits, 0's or not.

Any ideas? I have tried cstr(right(field,5)) and the query always
look
fine, it is just the transfertext function that messes it up.

TIA,
Randy K.