Remove leading zeros

B

Bill

I've read some of the previous threads regarding this and for some reason I
can't get the suggestions to work. I'd appreciate some help with this. I have
a table with a field that has 10 digits (numbers only) with leading zeros
where necessary to fill out to 10 digits. I need to be able to remove those
zeros. Conversely, I would like to be able to add the zeros back in at a
later date. I would like to know what to put in each of the boxes (Update to
& Criteria) on the update query page in order to accomplish these apparently
simple (except for me) tasks. Thanks in advance.
 
D

Dale Fye

To get rid of the leading zeros, you could do something like:

UPDATE yourTable
SET [YourFieldName] = Format(val([YourFieldName]), "#")

To add them back in, you could use:

UPDATE yourTable
SET [YourFieldName] = Format(val([yourFieldName]), "0000000000")

HTH
Dale
 
B

Bill

Thanks Dale,
I tried your suggestion and am still not getting results. Does the syntax
SET [Field name]... go in the
"Criteria" row of the query? When I run the query it says updating 0 rows.
Obviously I've got something screwed up.

Dale Fye said:
To get rid of the leading zeros, you could do something like:

UPDATE yourTable
SET [YourFieldName] = Format(val([YourFieldName]), "#")

To add them back in, you could use:

UPDATE yourTable
SET [YourFieldName] = Format(val([yourFieldName]), "0000000000")

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


Bill said:
I've read some of the previous threads regarding this and for some reason I
can't get the suggestions to work. I'd appreciate some help with this. I have
a table with a field that has 10 digits (numbers only) with leading zeros
where necessary to fill out to 10 digits. I need to be able to remove those
zeros. Conversely, I would like to be able to add the zeros back in at a
later date. I would like to know what to put in each of the boxes (Update to
& Criteria) on the update query page in order to accomplish these apparently
simple (except for me) tasks. Thanks in advance.
 
J

John Spencer

In the grid

Field: YourField
Update To: Format(val([YourFieldName]), "#")
Criteria: Is Not Null

Field: YourField
Update To: Format(val([yourFieldName]), "0000000000")

Criteria: Is Not Null

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


Dale Fye said:
To get rid of the leading zeros, you could do something like:

UPDATE yourTable
SET [YourFieldName] = Format(val([YourFieldName]), "#")

To add them back in, you could use:

UPDATE yourTable
SET [YourFieldName] = Format(val([yourFieldName]), "0000000000")

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


Bill said:
I've read some of the previous threads regarding this and for some reason
I
can't get the suggestions to work. I'd appreciate some help with this. I
have
a table with a field that has 10 digits (numbers only) with leading zeros
where necessary to fill out to 10 digits. I need to be able to remove
those
zeros. Conversely, I would like to be able to add the zeros back in at a
later date. I would like to know what to put in each of the boxes (Update
to
& Criteria) on the update query page in order to accomplish these
apparently
simple (except for me) tasks. Thanks in advance.
 
T

Tom Lake

Bill said:
I've read some of the previous threads regarding this and for some reason
I
can't get the suggestions to work. I'd appreciate some help with this. I
have
a table with a field that has 10 digits (numbers only) with leading zeros
where necessary to fill out to 10 digits. I need to be able to remove
those
zeros. Conversely, I would like to be able to add the zeros back in at a
later date. I would like to know what to put in each of the boxes (Update
to
& Criteria) on the update query page in order to accomplish these
apparently
simple (except for me) tasks. Thanks in advance.

Str(Val([YourNumber]))

will remove the Zeros

Right("0000000000" & [YourNumber], 10)

will restore them.

Tom Lake
 
B

Bill

Thanks for the help. I've managed to get my database organized the way I
needed it thanks to the help here. I appreciate it very much.
Bill
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Insert Varying Leading Zeros 2
Adding recognised Zeros 5
Ignore Leading Zeros in Mixed Alpha-Numeric Text Fields Select Query 5
Adding Zeros 7
Leading zeros 6
Add leading zeros 5
Leading zeros 3
Removing leading 0s 1

Top