Add leading zeros to data and remove hyphens...

D

deeds

I found the code below on here for removing hyphens and adding zeros to a txt
field. However, where do I put this code? How do I get this to do it's
work? Anyways....if anyone has other solutions..please let me know. All I
want to do is take a current txt field that contains data in the format of 1
and change it to 0000001 also, there are some data that have 1234567-1234 and
I want to remove the hyphen from these. (I would also like to remove the
last 4 digits, if someone can help there too.) Thanks in advance!

UPDATE CASTable SET CASTable.CAS =
Format(Replace([CAS],"-",""),"000000000");
 
O

Ofer Cohen

Hi,
This code
UPDATE CASTable SET CASTable.CAS =
Format(Replace([CAS],"-",""),"000000000");

It's an update query, you don't have to run update query to display the
right values.
You can create a select query, and create a new field in it to display it in
the right way

The new field

NewFieldName: Format(IIf(Instr([FieldName],"-"),
Left([FieldName],Len([FieldName])-5),[FieldName]),000000000)

If you still want to update the field, then I suggest you create a back up
first and then run the update query.
You can use the example I gave you to remove the - and four digits after it
(assuming that it's always 4)
 

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

Top