Update query to insert leading zero in certain records

I need to update a table named Keynotes with a field named Keys.  The problem is I need to add or insert a leading zero in the each record that has the following condition - 12345.A1  - that is I need the record to read 12345.A01 because there will be records that have 12345.A10.  So I need each record to have length of 9 characters. The records 12345.A1 thru 12345.A9 only have 8 characters.  Also the first five numbers can be any combination and the letter will be A thur Z.  
I would like the syntax to use a Query in the Design mode with the "Update to" row.  However, an SQL statement will be fine.  Help - I need this for tomorrow.
Cad CoderDeveloperAsked:
Who is Participating?
 
GRayLCommented:
Update Keynotes Set Keys=Left(Keys,7) & "0" & Right(Keys,1) where Len(Keys)=8;
0
 
mbizupCommented:
Try adding this VBA function to a module:

Public Function AddZero(str as string) as string
    if len(str) = 9  then
       AddZero = str
       exit Function
    else
       AddZero = Left(str,7) & "0" & right(str,1)
    end if
end function

And use this update query:

Update KeyNotes
Set NewKeys = AddZero(Keys)
Where nz(keys,"") <> ""

0
 
Leigh PurvisDatabase DeveloperCommented:
"Help - I need this for tomorrow"
It's a work day there tomorrow?  It's a holiday here... (apparently :-S )

Anyway...
Just to supplement to Miriam's answer... (as opposed to replace it)

You could look to limit the number of rows upon which you perform the update.
This is a good idea mostly for performance.  However - you'd lose a lot of the point of it if you used a function in the WHERE clause to determine the records to update (functions can't be optimized).

If you ran something like this - then only relevant rows would be checked - indeed, Miriam's function could even be made a little simpler - not having to check - and just creating the new string :-)

Update KeyNotes
Set NewKeys = AddZero(Keys)
Where keys Like "*.??"
0
 
Cad CoderDeveloperAuthor Commented:
To all,
Thanks for the quick responses.  I have added the VBA function to a module and then made a new query and pasted the following into the SQL view:

Update KeyNotes
Set NewKeys = AddZero(Keys)
Where nz(keys,"") <> ""

When I run the query it prompts for "Enter Parameter value: Keys "  What do I enter or am I not setting up the query properly?
0
 
mbizupCommented:
Is "Keys" the name of the field that needs to be updated?  If not replace "Keys" with the name of the field.  Also, you'll need to add a column titled "NewKeys" to your table.  I set up the code like that so it would put the new values in a seperate column instead of overwriting your existing data.  If you want to overwrite the existing column with the corrected values, revise the code like this:

Update KeyNotes
Set Keys = AddZero(Keys)
Where nz(keys,"") <> ""

Make a backup first!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.