• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

How do you upadate a field in an Access table?

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?
2 Solutions
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.

Gustav BrockCIOCommented:
For your last example that is:

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


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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now