Share via

IF Statements in Access

Anonymous
2014-05-26T12:20:41+00:00

I am reasonable proficient in Excel and use IF statements quite a lot. I have recently started using Access (2010) and would like run the equivalent of an IF statement but can't figure out what I need to do.

Problem Statement.

I would like to compare 2 text fields (say columns 1 & 2) and if they are the same i.e. both 'a', update column 3 with a value 'x'. If they are not equal, I want to update column 3 with the value 'y'. I have managed to get this working using 2 separate update queries but would like to do it with a single query if possible? 

Any suggestions would be much appreciated.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,810 Reputation points Volunteer Moderator
    2014-05-27T13:13:02+00:00

    Dave,

    First, you need to get away from the spreadsheet mentality. Access is not a spreadsheet, its a relational database and data structures are very different. 

    An IF function in Excel is closer to the IIF (Immediate IF) function in Access, but still different. An IF statement in Excel is a VBA statement and is the same as in Access.

    Second, as a general rule you don't store calculated values in Access tables. So using an Update query is the wrong approach. You do calculations in queries or on forms and reports, not in tables. The expression Mimmo gave you will work as a column in a query and can be used anyplace you want to display that result.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2014-05-27T08:21:49+00:00

    Hi Dave,

    in a query you can use the IIF statement. In your case you have

    Column3: IIF(Column1= Column2;"x";"y")

    Bye Mimmo

    Thanks. That works very nicely!

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2014-05-26T20:34:53+00:00

    IIf is a Function, not a statement so it can be used in a query's calculated field.  Since it can be calculated from other values in the same record, it should NOT be saved in a table (so an UPDATE query is not helpful).

    Whenever you need the x/y, just use a calculated field as Mimo suggested OR you can leave the query alone and display the x/y in a form/report text box by using the expression:

         =IIf(Column1= Column2, "x", "y")

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2014-05-26T12:56:04+00:00

    Hi Dave,

    in a query you can use the IIF statement. In your case you have

    Column3: IIF(Column1= Column2;"x";"y")

    Bye Mimmo

    1 person found this answer helpful.
    0 comments