Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-11-11
5
Medium Priority
?
2,233 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 700 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 700 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

604 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