How do you upadate a field in an Access table?

Posted on 2005-04-25
Last Modified: 2008-02-26
I have a Access table with two fields.  Field 1 is named activity code, Field two is named procedure code.  Data in field one is in the format of 90801h, 90802h, 90803h.  Data in Field 2 is in the format of Z123, Z124, Z125.  In this example, 90801h is = to Z123, 90802h = Z124, & 90803h = Z125. However the 90801 #'s have no relevance as to what I am trying to accomplish in the end

I want to make an Update query that will read the values in Field2 (the Z Numbers) and replace the H numbers with another value that is also equal and relevant to me.  For instance:  I would like for the query to read Z123 in field2 and then replace 90801h with 1420.  

I am trying to write an expression in my Update query that goes something like this:

If field2 is = to Z123 then Field1 is = to 1520 and If field2 is = to Z124 then Field 1 = 1530 and so on.  What is the correct syntax to do this?
Question by:hudmon
    LVL 8

    Expert Comment

    Hi Hudmon.
    You will need to run mulpiple update queries, each one updating one field for one criteria. Otherwise you can create a function using vb to loop through your recordset and update the field based on your if criteria.
    I dont think you will be able to create your if expression in an update query.

    LVL 48

    Accepted Solution

    For your last example that is:

      [Field 1] = (Mid([Field 2], 2) - 123 + 152) * 10

    LVL 6

    Assisted Solution


    If /gustav's formula is right then the query you need is as follows:
        UPDATE tblTable SET Field1=((Mid([Field2], 2) - 123 + 152) * 10)

    If such a formula is not possible then you will have to run these queries on by one
        UPDATE tblTable SET Field1='1520' WHERE Field2='Z123'
        UPDATE tblTable SET Field1='1530' WHERE Field2='Z124'
        etc ...

    (You will have to use your own name for the table in stead of tblTable)


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    Introduction If you are not already aware of what you could use a table with sequential integer values for in SQL, you can read Delimited String Parsing in SQL Server 2005 or later ( by BrandonGalderisi (h…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    732 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

    17 Experts available now in Live!

    Get 1:1 Help Now