Appending Text to Field

[Rob Mazoros]

Hello I am trying to append some leading digits (zeros) to a text field in access. I have some 4 and 5 digit numbers in this field and need to add one or two zeros to the front of the number. For example I need them to change from 4338 to 004338. These values are linked from ODBC and stored as text, so I have no choice on the field type. I have tried the wildcard find/replace which only seems to work for finding but not replacing. For example I searched for "????" and it found the 4 digit values that I am looking for but the replace function takes the wildcards verbatim "00????". Any help would be appreciated.

Rob Mazoros

 

[Cheryl Fischer]

I think you will need an update query, Rob. You can use the following in
the UpDate To: row of the query:

Format([MyField], "000000")


hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Hello I am trying to append some leading digits (zeros) to a text field in

access. I have some 4 and 5 digit numbers in this field and need to add one
or two zeros to the front of the number. For example I need them to change
from 4338 to 004338. These values are linked from ODBC and stored as text,
so I have no choice on the field type. I have tried the wildcard
find/replace which only seems to work for finding but not replacing. For
example I searched for "????" and it found the 4 digit values that I am
looking for but the replace function takes the wildcards verbatim "00????".
Any help would be appreciated.

 

[Rob Mazoros]

Thank You I will give that a try

Rob

 

[Stephane]

Try converting your string to integer, add 100000 to it,
convert the result back to text and then use a trim
function to extract the needed string. It should look like
this (logically):

Right(string(num(value)+100000), 5)

-----Original Message-----
Hello I am trying to append some leading digits (zeros)

to a text field in access. I have some 4 and 5 digit
numbers in this field and need to add one or two zeros to
the front of the number. For example I need them to
change from 4338 to 004338. These values are linked from
ODBC and stored as text, so I have no choice on the field
type. I have tried the wildcard find/replace which only
seems to work for finding but not replacing. For example
I searched for "????" and it found the 4 digit values that
I am looking for but the replace function takes the
wildcards verbatim "00????". Any help would be
appreciated.