Solved

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

Posted on 2010-11-11
5
1,778 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

861 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