Remove Zeroes in front of a number - help!

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!!!

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

"gholly" wrote in message
...
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!!!

gholly wrote:

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])

--
Marsh
MVP [MS Access]

On Fri, 16 Feb 2007 09:05:18 -0800, gholly wrote:

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");




--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail