Remove Leading Zero's

  • Thread starter Thread starter Matt via AccessMonster.com
  • Start date Start date
M

Matt via AccessMonster.com

I am working with a legacy system. The data comes to me with leading zero's.
The number of zeros varies so using a query and doing a left([FieldName],4)
or right([Fieldname],4) wont work.

Does anyone have any suggestions.

Thanks
Matt
 
Take a look at the VAL function. That converts a string to a number (which
should remove leading and trailing zeroes).
 
Yes - That works...

Thank you

John said:
Take a look at the VAL function. That converts a string to a number (which
should remove leading and trailing zeroes).
I am working with a legacy system. The data comes to me with leading zero's.
The number of zeros varies so using a query and doing a left([FieldName],4)
Click to expand...
[quoted text clipped - 4 lines]
Thanks
Matt
Click to expand...
Click to expand...
 
I am working with a legacy system. The data comes to me with leading zero's.
The number of zeros varies so using a query and doing a left([FieldName],4)
or right([Fieldname],4) wont work.

Does anyone have any suggestions.

Thanks
Matt
Click to expand...

If the Format property of the field is NOT 0000000 (as many 0's as the
number of characters permitted in the field), then since the Access
Number datatype does not store nor display leading zero's, it must be
a Text datatype field.
=Val([FieldName])
will return the number value of the text field, without the leading
zero's.
 
Back
Top