Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Syntax Error (Missing operator) - VBA code

Posted on 2008-06-23
7
Medium Priority
?
318 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 800 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 200 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
Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

 
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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

670 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