Add a leading zero to a field using an update query.

Windows XP, Access 2003, Novice user

I have a table with a field that has GL information.  The GL account information can have a leading zero. So "0100" is a valid entry.

However the data was originally in Excel and Excel removed the leading zero whenever the data was exported into Excel from the IBM host system.  I imported the Excel data that was provided to me into Access. I do not have access to the host system so I can't repull the data.  What I currently have in the table of the database is "100".

However I need to add the leading zero back to the data in my tables.  How do I do this with maybe an update query?  The value I want is "0100".

The max length of the value can be 4 characters.

Thanks in advance
mreid3847Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
you can, with

update table
set [field]=format([field],"0000")
where len([field])>0
0
 
njovinConnect With a Mentor Commented:
If it's in access you can right-click on the table and go to design view.  If the field is formatted as a "Number" then set the "Format" to "0000".  That will automatically put a leading zero in.

If the field is formatted as text and you don't want to format it as number for some reason, you can use this query to update it:

UPDATE table1 SET gl_account = "0" & gl_account where len(gl_account) = 3
0
 
mreid3847Author Commented:
Thanks for the quick response and assistance. Both worked.
Misty
0
 
mreid3847Author Commented:
Actually I viewed the results of trying both of these queries incorrectly.  After closer look at the results of both queries they both returned results/updates.  However ...

The solution by njovin selected a subset of records that were already a length of 4 and no update was applied.  For example it selected records 1000,2000,3120.

The solution provided by Capri did update all of the records that were missing the leading zeros.
0
 
Rey Obrero (Capricorn1)Commented:
so, what is the problem?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.