Appending Text to Field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Rob Mazoros said:
Hello I am trying to append some leading digits (zeros) to a text field in
Click to expand...
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.
 
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)
Click to expand...
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.
 
Back
Top