Query Expression

[funkykayli]

I have a query where I want to add some columns using an expression. The expression will be based on three other columns in the query. I am trying to accomplish this:

If PY = "2004" AND COVG = "WC" THEN new field = 250,000

I am not sure how to accomplish this. I tried using the expression builder but I am still stuck. Please help!?!?!

 

[Norie]

Perhaps this.

Iif([PY]&[COVG] = "2004WC", 250,000)

 

[funkykayli]

I tried using

Deductible: IIf([PY]=1994,[COVG]="WC","250000")

This seems to place 250000 in the field if it does NOT equal 1994 or WC.

 

[funkykayli]

Cool thanks! One more question. What if I have a bunch more conditions? How many conditions can you add to one expression?

 

[funkykayli]

As an example, I have the years:
1994
1995
1996
1997
1998
1999
200
2001

I have the COVG:
WC
GL

I have the state:
CA

If it is 1994,1995,1996,1997,1998,2000, or 2001 PY & WC, GL, or CA then the result should be 250,000. If it is 1999 PY for WC, CA it will be 200,000 but for GL it will be 150,000.

 

[funkykayli]

Is there an else statement I could use?

 

[Norie]

If you have all these conditions you really should take a different approach.

Try constructing a table all the combinations and the expected result(s).

Then you should be able to create a query that joins the 2 tables to return the expected results.