If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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!!! |
#2
|
|||
|
|||
Remove Zeroes in front of a number - help!
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!!! |
#3
|
|||
|
|||
Remove Zeroes in front of a number - help!
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] |
#4
|
|||
|
|||
Remove Zeroes in front of a number - help!
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 |
Thread Tools | |
Display Modes | |
|
|