Update query to insert leading zero in certain records

Posted on 2006-04-30
Last Modified: 2012-06-27
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.
Question by:Cad Coder
    LVL 61

    Expert Comment

    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
           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,"") <> ""

    LVL 44

    Expert Comment

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

    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 "*.??"
    LVL 44

    Accepted Solution

    Update Keynotes Set Keys=Left(Keys,7) & "0" & Right(Keys,1) where Len(Keys)=8;

    Author Comment

    by:Cad Coder
    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?
    LVL 61

    Assisted Solution

    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!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    754 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

    22 Experts available now in Live!

    Get 1:1 Help Now