Solved

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

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

Accepted Solution

by:
Rey Obrero 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 119

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

932 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now