asked on 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
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
Microsoft AccessVB Script
Last Comment
Rey Obrero (Capricorn1)
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
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.
so, what is the problem?
Misty