Remove leading zeros

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

 

[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

 

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

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.

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.

 

[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

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.

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.

 

[Tom Lake]

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

 

[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