Solved

Syntax Error (Missing operator) - VBA code

Posted on 2008-06-23
7
303 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
  • 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Field Properties in the DB Vs Bound Control on the form 9 33
convert null in sql server 12 34
is Microsoft Access going to Die? 9 53
Dirty form - conditional formatting 5 27
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

803 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