Solved

Syntax Error (Missing operator) - VBA code

Posted on 2008-06-23
7
313 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses
Course of the Month7 days, 22 hours left to enroll

617 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