how can I delete the four zeros at the end of a field?

[carl jordan]

I have a database of company names and DUNS numbers. Numerous people made
entries into this database. Some of the DUNS numbers have four 0s at the
end, and some do not. For my purposes they all have to be the same length --
either with the four zeros or without them, but all the same length. I
created two queries to separate the two sets of numbers.

They are in text format, and some begin with a 0. So, if I convert them to
numbers and multiply or divide by 10,000 they still will not be right,
because the starting 0 will be dropped. They were also, BTW, provided in a
number format, but the starting 0s are all gone, and hence the lengths vary.

So, I would like either to add four zeros to one group of these number
(about 7,800) or remove the four zeros from the rest (about 165,000). Either
will meet my needs, so long as they are the same length, but I cannot figure
out how to do this.

Any help will be appreciated.

Thanks in advance.

 

[Golfinray]

With the ones that have zeroes, try (let's say you have 12 number, the last
four zeroes)
Fieldmid([tablename!fieldname],1,8)) this would return your number
starting at first digit and going through eight. In other words let's say
your number is 234221580000, this will return 23422158. you may have to play
with the numbers (1,8) to get it the way you want it.

 

[KARL DEWEY]

Try this ---
New_NUM: Left(Val([YourField]) & "0000000000000000", 12)

Val([YourField]) removes all leading zeros.
& "0000000000000000" adds zeros.
Left( ,12) shortens the text number to 12 digits.