Removing leading Zeros (Access 2003 SP2)

Topic

New Reply

I import a table where the Employee Number is formatted: EMP_00004567 (TEXT Field)

I use a partial Text extract Query to eliminate the “EMP_” leaving me with 00004567

So far I remove the Leading Zero manuall by: 1) Changing the TEXT field to a NUMBER field in Design View and SAVE. Then changing it back to TEXT. (It needs to be in TXT)

I want to automate this as it is updated weekly.

What Update query can I use to remove the LEADING zeroes without having to change the Table Design?

Thanks

Reply | Quote

Replies

You could use an expression like this in the update query based on the imported table:

CStr(Val(Mid([Employee Number],4)))

The Mid function removes the “EMP_” part, Val converts the remainder to a number and CStr converts this number back to text.

Reply | Quote

Thank yo… I will test

Reply | Quote