Query Expression

funkykayli

Board Regular
Joined
Apr 25, 2007
Messages
183
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!?!?!
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Perhaps this.

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

funkykayli

Board Regular
Joined
Apr 25, 2007
Messages
183
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

Board Regular
Joined
Apr 25, 2007
Messages
183
Cool thanks! One more question. What if I have a bunch more conditions? How many conditions can you add to one expression?
 

funkykayli

Board Regular
Joined
Apr 25, 2007
Messages
183

ADVERTISEMENT

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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,139
Messages
5,599,951
Members
414,350
Latest member
ajesh27

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top