Solved

Syntax Error (Missing operator) - VBA code

Posted on 2008-06-23
7
299 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
Comment Utility
>>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
Comment Utility
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
Comment Utility
The error is this line:
and ((HCFA.[Ins Co Name])=(" & cboInsCo & "));"

it should be:
and ((HCFA.[Ins Co Name])='" & cboInsCo & "');"
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 19

Expert Comment

by:frankytee
Comment Utility
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
Comment Utility
>>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
Comment Utility
Thanks.
0
 
LVL 29

Expert Comment

by:Badotz
Comment Utility
No worries - glad to help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now