Add a leading 0 if less than 5 characters

  • Thread starter Thread starter Steve Roberts
  • Start date Start date
S

Steve Roberts

I am in process of converting some data into a more usable format. I need a
query that will add a leading 0 if there are less than 5 characters ie
change 1111 into 01111.
Table is Employee Field is EmployeeNumber. Access 2002

Thanks in advance.

Steve
 
Steve

The key is in your statement "...less than 5 characters...". Unless that
"EmployeeNumber" is going to be added or subtracted, it isn't really a
"number", and doesn't need a numeric data type. A similar thing happens
when folks want to enter zip codes for places on the East Coast of the US --
those zips start with a zero, but a numeric data type drops leading zeros.

Do you need to have the leading zero(s) for esthetic purposes, or will the
EmployeeNumbers be passed to an application that MUST see five places?

Good luck

Jeff Boyce
<Access MVP>
 
Right$("00000" & EmployeeNumber, 5)

Simple, huh? :D

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Back
Top