Help with figuring what I am doing wrong with Query Criteria

[perryc]

So, I have a query that pull up data based on Criteria on a form.


[A?P?R?]
Criteria: IIf([Forms]![Frm_Report_PayorMix]![FinalDecision]="*",([Referrals].[A?P?R?])<>"F/U",[Forms]![Frm_Report_PayorMix]![FinalDecision])

I thought this will generate a <>"F/U" if the FinalDecision box = * ,otherwise, will just be the value on the FinalDecision Box




SELECT Referrals.ReferralDate, Referrals.[A?P?R?], Referrals.CaseComplete, Referrals.RefType, Referrals.NN, Referrals.[ROC?], Referrals.Patient, Referrals.RefSource, Referrals.Zip, Referrals.Dx, Referrals.Payor, Referrals.InsSpecific, Referrals.[ACO?], Referrals.ExpHHSOC, Referrals.ExpPTEval, Referrals.ID, Referrals.DOB, Referrals.M0102, Referrals.Attending, Referrals.[BSHSIDoc?], Referrals.EnteredBy, Referrals.DeclineReason, Referrals.[SN?], Referrals.[PT?], Referrals.[OT?], Referrals.[ST?], Referrals.OtherServices, Referrals.RefProcessed, Referrals.[Bundled?]
FROM Referrals
WHERE (((Referrals.[A?P?R?])=IIf([Forms]![Frm_Report_PayorMix]![FinalDecision]="*",([Referrals].[A?P?R?])<>"F/U",[Forms]![Frm_Report_PayorMix]![FinalDecision])) AND ((Referrals.RefType) Like [Forms]![Frm_Report_PayorMix]![OtherRef]) AND ((Referrals.[ROC?])=False) AND ((Referrals.ExpHHSOC)>=[Forms]![Frm_Report_PayorMix]![From] And (Referrals.ExpHHSOC)<DateAdd("d",1,[Forms]![Frm_Report_PayorMix]![Thru])))


ORDER BY Referrals.[A?P?R?], Referrals.Patient;

[June7]

Asterisk (*) is wildcard character. Are you trying to find the actual character in data? Show sample data.

Strongly advise not to use punctuation nor special characters in naming convention.

[perryc]

I changed that to "ALL" and the result is the same. Not sure what I am doing wrong in the Criteria. What I want to see is: if the Como Box value = "ALL", the criteria for the query = <>"F/U", otherwise, just use the Value in the Combo Box.

The reason why I do a <>"F/U" because I want to display everything else.

[davegri]

Don't know if this is missing the point, but the expression

([Referrals].[A?P?R?])<>"F/U"

is a comparison and will resolve as true or false.

[June7]

Review https://stackoverflow.com/questions/...y-returns-true

Instead of popup input prompt shown in examples, use reference to control on form.

Also, http://allenbrowne.com/ser-62.html

[CJ_London]

[A?P?R?]
Criteria: IIf([Forms]![Frm_Report_PayorMix]![FinalDecision]="*",([Referrals].[A?P?R?])<>"F/U",[Forms]![Frm_Report_PayorMix]![FinalDecision])

you can't use iif's in criteria in this way, you use 'and's' and 'or's' with appropriate bracketing

criteria
<>"F/U" AND ([Forms]![Frm_Report_PayorMix]![FinalDecision] OR [Forms]![Frm_Report_PayorMix]![FinalDecision]="*")

[perryc]

I see. Ty so much. I try it and it works!!! Ty