Formula Help

[AGoodGuy]

I have a query that I created. I have two fields that I matched from the queries: 26 Daily Policies Expired.PolicyNumber and 03 Renewal Offer Analysis.Policy Number. If the policy number is showing for 26 Daily Policies Expired.PolicyNumber and not showing for 03 Renewal Offer Analysis.Policy Number I want it to say "No Match".

In my query I have this: IIF([26Daily Policies Expired.PolicyNumber]<>[03Renewal Offer Analysis.Policy Number],"No Match")

It is not giving me what I need. In other words if the column is blank I want it to show "No Match". Of course if the two fields match then the policy number for both fields will show....any suggestions??

 

[dhookom]

Blank/Null fields won't match anything (even each other). IIf() expects 3 arguments (2 commas), not 2 (1 comma).

Try convert everything to strings with:

 this: IIF([26Daily Policies Expired].[PolicyNumber] & "" <>[03Renewal Offer Analysis].[Policy Number] & "","No Match",[26Daily Policies Expired].[PolicyNumber])

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Match: IIf(nz(,"")<>nz([C],""),"No Match","")

You need to handle nulls with NZ