VRTech
asked on
Syntax Error (Missing operator) - VBA code
I have an Access form with four fields which I am passing to a query in order to populate a report. The query returns the following error: Runtime Error 3075 (missing operator). Here's the code for the query (just the criteria portion):
"HCFA.[Other Payment], HCFA.[11b], HCFA.[Payor ID], HCFA.[Bill To Plan Name], HCFA.[Bill To Address 1], HCFA.[Bill To Address 2], " & _
"HCFA.[Bill To City], HCFA.[Bill To State], HCFA.[Bill To ZIP], HCFA.[Insured Gender], HCFA.[Insured Date Of Birth], " & _
"HCFA.[JDA SKU 1], HCFA.[JDA SKU 2], HCFA.[JDA SKU 3], HCFA.[JDA SKU 4], HCFA.[JDA SKU 5], HCFA.[JDA SKU 6], HCFA.[Patient DOB], " & _
"HCFA.[Plan ID], HCFA.NewDate, HCFA.NewBirthDate, HCFA.NewInsuredBirthDate, HCFA.[DOS Period], HCFA.[DOS Year], " & _
"HCFA.[Total Ticket], HCFA.[Paid POS], HCFA.[AR POS], HCFA.[Billing Adjustment], HCFA.Balance, HCFA.Ready2Print FROM HCFA " & _
"where ((HCFA.[Date of Order]) between (#" & cboBeginningDate & "#) and (#" & cboEndingDate & "#)) and ((HCFA.[Location Number])=cstr(" & cboOfficeNumber & ")) and ((HCFA.[Ins Co Name])=(" & cboInsCo & "));"
The attached file shows the error message box, and it has the values in the message so I believe the code is actually getting the parameters.
Any ideas?
Thanks.
Bret
Syntax-Error.doc
"HCFA.[Other Payment], HCFA.[11b], HCFA.[Payor ID], HCFA.[Bill To Plan Name], HCFA.[Bill To Address 1], HCFA.[Bill To Address 2], " & _
"HCFA.[Bill To City], HCFA.[Bill To State], HCFA.[Bill To ZIP], HCFA.[Insured Gender], HCFA.[Insured Date Of Birth], " & _
"HCFA.[JDA SKU 1], HCFA.[JDA SKU 2], HCFA.[JDA SKU 3], HCFA.[JDA SKU 4], HCFA.[JDA SKU 5], HCFA.[JDA SKU 6], HCFA.[Patient DOB], " & _
"HCFA.[Plan ID], HCFA.NewDate, HCFA.NewBirthDate, HCFA.NewInsuredBirthDate, HCFA.[DOS Period], HCFA.[DOS Year], " & _
"HCFA.[Total Ticket], HCFA.[Paid POS], HCFA.[AR POS], HCFA.[Billing Adjustment], HCFA.Balance, HCFA.Ready2Print FROM HCFA " & _
"where ((HCFA.[Date of Order]) between (#" & cboBeginningDate & "#) and (#" & cboEndingDate & "#)) and ((HCFA.[Location Number])=cstr(" & cboOfficeNumber & ")) and ((HCFA.[Ins Co Name])=(" & cboInsCo & "));"
The attached file shows the error message box, and it has the values in the message so I believe the code is actually getting the parameters.
Any ideas?
Thanks.
Bret
Syntax-Error.doc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
also use the office number part that Badotz posted, your original:
...and ((HCFA.[Location Number])=cstr(" & cboOfficeNumber & "))
should be:
..and ((HCFA.[Location Number])='" & cstr(cboOfficeNumber) & "')
this of course assumes HCFA.[Location Number] is a text field otherwise you would not need the cstr function.
...and ((HCFA.[Location Number])=cstr(" & cboOfficeNumber & "))
should be:
..and ((HCFA.[Location Number])='" & cstr(cboOfficeNumber) & "')
this of course assumes HCFA.[Location Number] is a text field otherwise you would not need the cstr function.
>>I don't believe the cboOfficeNumber parameter is the culprit.
I do not care what you "believe", your syntax is wrong.
((HCFA.[Location Number])=cstr(" & cboOfficeNumber & "));" is wrong. It shoulfd be:
((HCFA.[Location Number])='" & cstr(cboOfficeNumber) & "');"
The fact that your SQL works in purely coincidental. If you validated your SQL, you would see that.
I do not care what you "believe", your syntax is wrong.
((HCFA.[Location Number])=cstr(" & cboOfficeNumber & "));" is wrong. It shoulfd be:
((HCFA.[Location Number])='" & cstr(cboOfficeNumber) & "');"
The fact that your SQL works in purely coincidental. If you validated your SQL, you would see that.
ASKER
Thanks.
No worries - glad to help.
ASKER
"HCFA.[Total Ticket], HCFA.[Paid POS], HCFA.[AR POS], HCFA.[Billing Adjustment], HCFA.Balance FROM HCFA " & _
"where ((HCFA.[Date of Order]) between " & _
"(#" & cboBeginningDate & "#) and (#" & cboEndingDate & "#)) and ((HCFA.[Location Number])=cstr(" & cboOfficeNumber & "));"
I don't believe the cboOfficeNumber parameter is the culprit. No it doesn't like resequencing the cboOfficeNumber syntax.
Any other ideas?