Remove Zeroes in front of a number - help!

[Guest]

hoping someone can help me figure this out. i'm not sure how i would need to
do this. basically i have 2 different tables with what should be the same
account #. however, 1 table is from a report that gets sent to me, and the
way their system works it adds zeroes to the front of the number (i think to
fill 8 character slots). however the other table, the number is just the
number regardless of the # of characters. i need to link these 2 fields but
can't because it is recognizing them as different.

Example:

Table 1, Field name: Account Code = 00005279
Table 2, Field name: Account Code = 5279

Please note that there is no set # of characters...so some of the account
codes are actually 8 numbers long, and some could be only 1 or 2.

hope this makes sense and that someone can help fast! thanks so much!!!

 

[John Spencer]

You can apply Val to the text string and it will turn the string into a
number variable.

Val([Account code]) will strip off the leading zeroes.

or you can format your account code to turn it into a string
Format([table2].[AccountCode],"00000000")

Beyond that you didn't say what you need to do, so ...

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marshall Barton]

hoping someone can help me figure this out. i'm not sure how i would need to
do this. basically i have 2 different tables with what should be the same
account #. however, 1 table is from a report that gets sent to me, and the
way their system works it adds zeroes to the front of the number (i think to
fill 8 character slots). however the other table, the number is just the
number regardless of the # of characters. i need to link these 2 fields but
can't because it is recognizing them as different.

Example:

Table 1, Field name: Account Code = 00005279
Table 2, Field name: Account Code = 5279

Please note that there is no set # of characters...so some of the account
codes are actually 8 numbers long, and some could be only 1 or 2.

Use the query's SQL view to change the Join's ON expression
to:
ON table2.[Account Code] = CLng(table1.[Account Code])

 

[fredg]

hoping someone can help me figure this out. i'm not sure how i would need to
do this. basically i have 2 different tables with what should be the same
account #. however, 1 table is from a report that gets sent to me, and the
way their system works it adds zeroes to the front of the number (i think to
fill 8 character slots). however the other table, the number is just the
number regardless of the # of characters. i need to link these 2 fields but
can't because it is recognizing them as different.

Example:

Table 1, Field name: Account Code = 00005279
Table 2, Field name: Account Code = 5279

Please note that there is no set # of characters...so some of the account
codes are actually 8 numbers long, and some could be only 1 or 2.

hope this makes sense and that someone can help fast! thanks so much!!!

In the one table, you have a text datatype field (which can include
preceding zero's), and in the other table, you have a Number datatype
field (which cannot include preceding zero's).

If you wish to remove the zeros from the Text datatype field, use:
= Val([AccountCode])

Note: if the [AccountCode] is not used for math (which it isn't), it
should be Text datatype.
To change a Number datatype field to Text:
=Format([NumberField],"00000000")

To store it as text (in a text field), run an update query:
Update YourTable Set YourTable.[TextField] =
Format([NumberField],"00000000");