By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,306 Members | 1,621 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,306 IT Pros & Developers. It's quick & easy.

How to update a filed with multiple conditions?

P: 1
I have a table, what I need to do is

if Field 1= a, field 2=b, field 3=c, then field 4 update to xxxx;
iif Field 1= a, field 2=b, field 3=d, then field 4 update to xxxxxx,
if Field 1= z, field 2=e, field 3=g, then field 4 update to xxxxxxxxx,
if Field 1= y, field 2=h, field 3=s, then field 4 update to xxxxxxxxxxx,
so on

I wold like to know how to do it in Access?

If I use Update query in Access, I need to write a lot of queries like:
Update Table Set ... Where .. ; Can Update query add condition?

Can I use VBA or ADO?

Thank you very much for your help !
Sep 11 '07 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
You can do this in a query as follows:

Expand|Select|Wrap|Line Numbers
  1. UPDATE TableName SET [Field4]="xxxx"
  2. WHERE [Field1]= a 
  3. AND [Field2]=b, 
  4. [Field3]=c;
However, for better Control you will probably need to use a recordset in VBA with nested IF statements. I am using DAO as I personally prefer it. Something like the following:

Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim rs As DAO.Recordset
  3.  
  4.    Set db = CurrentDb
  5.    Set rs = db.OpenRecordset("TableName")
  6.  
  7.    rs.MoveFirst
  8.    Do until rs.EOF
  9.       If rs!Field1 = "a" Then
  10.          If rs!Field2 = "b"  And rs!Field3 = "c" Then
  11.             rs.Edit
  12.             rs!Field4 = "xxxxx"
  13.             rs.Update
  14.          Else
  15.             rs.Edit
  16.             rs!Field4 = "xxx"
  17.             rs.Update
  18.          End If
  19.       ElseIf rs!Field1 = "z" Then
  20.          ' and so on
  21.       ElseIf rs!Field1 = "y" Then
  22.          ' and so on
  23.       Else
  24.          ' if none are true
  25.       End If
  26.       rs.MoveNext
  27.    Loop
  28.  
  29.    rs.Close
  30.    Set rs = Nothing
  31.    Set db = Nothing
  32.  
You just need to figure out the logic.
Sep 13 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.