Criteria using IIF statement 1

[realstandman]

I have a query that I'm using the criteria to pull from a form. If the form = "All Medical" then I need it to use 2 of the value list for criteria. Here is what I have. It gives me the expression is too complex error.

IIf([Forms]![frmQA_Report_Interface]![Producttype]="All Medical","HCFA" And "UB",[Forms]![frmQA_Report_Interface]![Producttype])

The list values are
HCFA
UB
All Medical
Flex
Dental
Pharmacy

Thanks for any suggestions.

Don't think and the solution will come.

 

[PHV]

What is the WHERE clause of the actually too complex SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[realstandman]

There are alot of references to the interface form for criteria. Here is the whole SQL.

SELECT AUDIT.Audit_Number, GROUPS.Group_Name, AUDIT.Audit_Type, AUDIT.Audit_Product, AUDIT.Claim_Number, AUDIT.Claim_Amt_Paid, AUDIT.Claim_Should_Have_Paid, Int([lines_audited]) AS [Lines Audited], Int([lines_Correct]) AS [Lines Correct], AUDIT.Member_Id, AUDIT.Group_Id, AUDIT.Special_Audit, AUDIT.Auditor_ID, AUDIT.Audit_Date, Trim([first_Name]) & " " & Trim([Last_Name]) AS MBSAnalystNAME
FROM MBS_DIRECTORY INNER JOIN (GROUPS INNER JOIN AUDIT ON GROUPS.Group_Id = AUDIT.Group_Id) ON MBS_DIRECTORY.Member_Id = AUDIT.Member_Id
WHERE (((AUDIT.Audit_Number) Between [Forms]![frmQA_Report_Interface]![Combo46] And [Forms]![frmQA_Report_Interface]![Combo50]) AND ((AUDIT.Audit_Type) Like ([Forms]![frmQA_Report_Interface]![CboAudit_Type])) AND ((AUDIT.Audit_Product)=IIf([Forms]![frmQA_Report_Interface]![Producttype]="All Medical",(AUDIT.Audit_Product)="HCFA" And (AUDIT.Audit_Product)="UB",[Forms]![frmQA_Report_Interface]![Producttype])) AND ((AUDIT.Member_Id) Between [Forms]![frmQA_Report_Interface]![cbofromMBSID] And [Forms]![frmQA_Report_Interface]![cbotoMBSID]) AND ((AUDIT.Group_Id) Between ([Forms]![frmQA_Report_Interface]![fromgroup]) And ([Forms]![frmQA_Report_Interface]![togroup])) AND ((AUDIT.Special_Audit) Like ([Forms]![frmQA_Report_Interface]![specialaudit])) AND ((AUDIT.Auditor_ID) Like ([Forms]![frmQA_Report_Interface]![cboauditor])) AND ((AUDIT.Audit_Date) Between [Forms]![frmQA_Report_Interface]![FromDate] And [Forms]![frmQA_Report_Interface]![toDate]) AND ((GROUPS.Term_Date)=#12/31/9999#));



Don't think and the solution will come.

 

[realstandman]

IIf([Forms]![frmQA_Report_Interface]![Producttype]="All Medical","HCFA" And "UB",[Forms]![frmQA_Report_Interface]![Producttype])


This is the one piece not working. Everything else works fine if I take this out.

Don't think and the solution will come.

 

[PHV]

I hate this stupid query grid generating such weird SQL code ...
What about this ?

SELECT A.Audit_Number, G.Group_Name, A.Audit_Type, A.Audit_Product, A.Claim_Number, A.Claim_Amt_Paid
, A.Claim_Should_Have_Paid, Int([lines_audited]) AS [Lines Audited], Int([lines_Correct]) AS [Lines Correct]
, A.Member_Id, A.Group_Id, A.Special_Audit, A.Auditor_ID, A.Audit_Date
, Trim([first_Name]) & " " & Trim([Last_Name]) AS MBSAnalystNAME
FROM MBS_DIRECTORY AS M
INNER JOIN (GROUPS AS G INNER JOIN AUDIT AS A ON G.Group_Id = A.Group_Id)
ON M.Member_Id = A.Member_Id
WHERE (A.Audit_Number Between [Forms]![frmQA_Report_Interface]![Combo46] And [Forms]![frmQA_Report_Interface]![Combo50])
 AND A.Audit_Type Like [Forms]![frmQA_Report_Interface]![CboAudit_Type]
 AND ((A.Audit_Product In ('HCFA','UB') AND [Forms]![frmQA_Report_Interface]![Producttype]='All Medical')
   OR (A.Audit_Product=[Forms]![frmQA_Report_Interface]![Producttype]))
 AND (A.Member_Id Between [Forms]![frmQA_Report_Interface]![cbofromMBSID] And [Forms]![frmQA_Report_Interface]![cbotoMBSID])
 AND (A.Group_Id Between [Forms]![frmQA_Report_Interface]![fromgroup] And [Forms]![frmQA_Report_Interface]![togroup])
 AND A.Special_Audit Like [Forms]![frmQA_Report_Interface]![specialaudit]
 AND A.Auditor_ID Like [Forms]![frmQA_Report_Interface]![cboauditor]
 AND (A.Audit_Date Between [Forms]![frmQA_Report_Interface]![FromDate] And [Forms]![frmQA_Report_Interface]![toDate])
 AND G.Term_Date=#12/31/9999#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[realstandman]

Bada Bing!!! Have a star!!!

Don't think and the solution will come.

 

[JA3395]

How about some VBA?

Function GetResult(ByVal Producttype As Variant, ByVal Audit_Product As Variant) As Boolean

    GetResult = False
    
    Select Case Producttype
    Case "All Medical"
        Select Case Audit_Product
        Case "HCFA", "UB"
            GetResult = True
        End Select
    End Select
    
End Function

use:

AND ((GetResult([Forms]![frmQA_Report_Interface]![Producttype],A.Audit_Product)=True)

J.