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.
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.
"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 "*.??"
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 "*.??"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,"") <> ""