Solved

Syntax Error (Missing operator) - VBA code

Posted on 2008-06-23
7
308 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:VRTech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 29

Accepted Solution

by:
Badotz earned 200 total points
ID: 21851111
>>and ((HCFA.[Location Number])=cstr(" & cboOfficeNumber & ")) and ((HCFA.[Ins Co Name])=(" & cboInsCo & "));"

should be:

and ((HCFA.[Location Number])=" & cstr(cboOfficeNumber) & ") and ((HCFA.[Ins Co Name])=(" & cboInsCo & "));"

If that doesn't work wrap the office number in apostrophes:

and ((HCFA.[Location Number])='" & cstr(cboOfficeNumber) & "') and ((HCFA.[Ins Co Name])=(" & cboInsCo & "));"
0
 

Author Comment

by:VRTech
ID: 21851381
this is actually a second SQL statement that runs after the code determines if the cboInsCo drop down has been populated.  When it hasn't, another SQL statement runs that does not include the cboInsCo parameter being passed.  That statement looks like this, and it is working:

        "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?
0
 
LVL 19

Assisted Solution

by:frankytee
frankytee earned 50 total points
ID: 21852703
The error is this line:
and ((HCFA.[Ins Co Name])=(" & cboInsCo & "));"

it should be:
and ((HCFA.[Ins Co Name])='" & cboInsCo & "');"
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 19

Expert Comment

by:frankytee
ID: 21852715
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.
0
 
LVL 29

Expert Comment

by:Badotz
ID: 21854621
>>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.
0
 

Author Closing Comment

by:VRTech
ID: 31469973
Thanks.
0
 
LVL 29

Expert Comment

by:Badotz
ID: 21859283
No worries - glad to help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question