• 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?
0
hudmon
Asked:
hudmon
2 Solutions
 
infolurkCommented:
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.

HTH
SFH
0
 
Gustav BrockCIOCommented:
For your last example that is:

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

/gustav
0
 
rvooijsCommented:
Hi,

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)

Success,
Robert
0

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