Solved

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

Posted on 2010-11-11
5
1,854 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:mreid3847
  • 2
  • 2
5 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 175 total points
ID: 34112615
you can, with

update table
set [field]=format([field],"0000")
where len([field])>0
0
 
LVL 3

Assisted Solution

by:njovin
njovin earned 175 total points
ID: 34112639
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
 

Author Closing Comment

by:mreid3847
ID: 34112733
Thanks for the quick response and assistance. Both worked.
Misty
0
 

Author Comment

by:mreid3847
ID: 34112990
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34113077
so, what is the problem?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question