Hi all.

I am trying to build a query that combines several fields. The situation is as follows....

1. If a is > b then i need a
2. If b is > a then i need b
3. If a is null then b is not null
4. If b is null then a if a is not null
5. If a and b are null then '999999'

I have everything taken care of up to If 4. The problem i am having is that when create the sql language for 5 it is adding 999999 to the filed when step 3 or 4 are the case. See languange below.

Any help would be greatly appreciated.

IIf(MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg]>MSC.[TDS<2500_S_GW_NR_Model_mg/kg],MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg]) & IIf(MSC.[TDS<2500_S_GW_NR_Model_mg/kg]>MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg],MSC.[TDS<2500_S_GW_NR_Model_mg/kg]) & IIf(MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg] Is Null,MSC.[TDS<2500_S_GW_NR_Model_mg/kg]) & IIf(MSC.[TDS<2500_S_GW_NR_Model_mg/kg] Is Null,MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg]) & IIf(MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg],MSC.[TDS<2500_S_GW_NR_Model_mg/kg] Is Null,'999999') AS MSC_SGW

Nevermind i figured it out.

To satisfy this...I am trying to build a query that combines several fields. The situation is as follows....

1. If a is > b then i need a
2. If b is > a then i need b
3. If a is null then b is not null
4. If b is null then a if a is not null
5. If a and b are null then '999999'Try changing all of this...IIf(MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg]>MSC.[TDS<2500_S_GW_NR_Model_mg/kg],MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg]) &

IIf(MSC.[TDS<2500_S_GW_NR_Model_mg/kg]>MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg],MSC.[TDS<2500_S_GW_NR_Model_mg/kg]) &

IIf(MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg] Is Null,MSC.[TDS<2500_S_GW_NR_Model_mg/kg]) &

IIf(MSC.[TDS<2500_S_GW_NR_Model_mg/kg] Is Null,MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg]) &

IIf(MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg],MSC.[TDS<2500_S_GW_NR_Model_mg/kg] Is Null,'999999') AS MSC_SGWTo this...SELECT Switch(a > b, a,

b > a, b,

a Is Null AND b Is Not Null, b,

b Is Null AND a Is Not Null, a,

a Is Null AND b Is Null, "999999")