Solved

Error 3075. DAO.Database Syntax error(missing operator) in SQL statement

Posted on 2010-09-24
8
986 Views
Last Modified: 2012-05-10
When executing the attached SQL statement in a function I get "Error 3075 in query expression 'PUB_INvcHead.CustNum=tblCust.CustNum INNER JOIN PUB_ShipTo ON (PUB_InvcDtl.CustNum = PUB_Shipto.CustNum' ". I created the statement by copying a workable SQL statement. I added the PUB_ShipTo to it. What is missing? I cannot see anything amiss.
Please let me know if there is a better clearer method of attaching the code.
Thanks
strSQL = "SELECT tblCust.CustID, tblCust.Name, tblCust.SupplierNumber, " & _
    "PUB_InvcHead.CustNum, PUB_InvcHead.InvoiceDate, Format([InvoiceDate]," & Chr(34) & invdat & Chr(34) & ") AS InvDatEDI, " & _
    "PUB_InvcHead.InvoiceType, PUB_InvcHead.InvoiceNum, PUB_InvcHead.InvoiceAmt, PUB_InvcDtl.InvoiceLine, " & _
    "qrsOrderHedDtlInv.OrderNum, qrsOrderHedDtlInv.PONum, qrsOrderHedDtlInv.BYName, qrsOrderHedDtlInv.BYPlantCode, " & _
    "IIf([ItemNum] Is Not Null And [ItemNum]<>" & Chr(34) & Chr(34) & ",[ItemNum],1) AS ItNum, " & _
    "PUB_InvcDtl.PartNum, PUB_InvcDtl.ShipDate, PUB_InvcDtl.PackNum, PUB_InvcDtl.OurShipQty, " & _
    "PUB_InvcDtl.UnitPrice, NZ([XPartNum]," & Chr(34) & Chr(34) & ") AS CustPNum, PUB_InvcDtl.ExtPrice, " & _
    "PUB_InvcDtl.PackLine, tblCust.EDICode, PUB_ShipTo.Name As STName, PUB_ShipTo.Address1, PUB_ShipTo.Address2, " & _
    "PUB_ShipTo.City, PUB_ShipTo.State, PUB_ShipTo.Zip, PUB_ShipTo.Country " & _
    "FROM ((PUB_InvcHead INNER JOIN PUB_InvcDtl ON PUB_InvcHead.InvoiceNum = PUB_InvcDtl.InvoiceNum) " & _
    "LEFT JOIN qrsOrderHedDtlInv ON (PUB_InvcDtl.OrderNum = qrsOrderHedDtlInv.OrderNum) AND " & _
    "(PUB_InvcDtl.OrderLine = qrsOrderHedDtlInv.OrderLine)) " & _
    "INNER JOIN tblCust ON (PUB_InvcHead.CustNum = tblCust.CustNum) " & _
    "INNER JOIN PUB_ShipTo ON (PUB_InvcDtl.CustNum = PUB_ShipTo.CustNum) AND (PUB_InvcDtl.ShipToNum = PUB_ShipTo.ShipToNum) " & _
    "WHERE (((PUB_InvcHead.CustNum) =" & [Forms]![frmInvEntry]![cmbCustNum] & ") And " & _
    "((PUB_InvcHead.InvoiceDate) =#" & [Forms]![frmInvEntry]![txtInvDate] & "#) And " & _
    "((PUB_InvcHead.InvoiceType) = " & Chr(34) & SType & Chr(34) & ") And " & _
    "((tblCust.EDICode) Is Not Null And (tblCust.EDICode) <> " & Chr(34) & Chr(34) & ")) " & _
    "ORDER BY PUB_InvcHead.InvoiceNum, PUB_InvcDtl.InvoiceLine;"

Open in new window

0
Comment
Question by:StampIT
  • 5
  • 2
8 Comments
 
LVL 57
ID: 33755729
<<Please let me know if there is a better clearer method of attaching the code.>>
  Nope, that's it.
<< What is missing? >>

 Probably a ).   I would try and break it down.  First, chop off the WHERE and ORDER BY clauses and try executing.  If an error, then eliminate all the SELECT's fields except for one field.  If it still fails (and this is probably where it is), then it's the joins.
User the query designer to get the join syntax you need.  You can design it graphically and then switch to SQL view, or play in SQL view and build the statement up slowly switching to design view as you progress.  If you get an error, then you know what you just did is at fault.
JimD.
0
 
LVL 57
ID: 33755748
BTW,  you've got a couple of these in there:
& Chr(34) & Chr(34)
 Which doesn't look quite right.  Either you meant to stick a value in there and didn't, or you included an extra quote (chr$(34)) by mistake.
JimD.
0
 
LVL 2

Expert Comment

by:godspropy
ID: 33755845
I'm no MS Access expert but I have never seen # characters used that way in SQL.

I would try replacing:

#" & [Forms]![frmInvEntry]![txtInvDate] & "#

with:

" & [Forms]![frmInvEntry]![txtInvDate] & "
0
Industry Leaders: 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!

 
LVL 57
ID: 33756039
godspropy,
#'s are delimiters to indicate date/time fields in JET SQL
JimD
0
 

Author Comment

by:StampIT
ID: 33771265
JimD,
   Thanks for the comments. To create the SQL statement that generates the error I copied from the attached code. The attached runs fine. I added some fields and the last INNER JOIN for the PUB_ShipTo table. So doing as you suggest, eliminating the last INNER JOIN I know the statement will work. Also I did create the query graphically first. The way I have the last INNER JOIN is the way it is when I switch the graphical query to SQL. Is there anything else I can try?
strSQL = "SELECT tblCust.CustID, tblCust.Name, tblCust.SupplierNumber, " & _
    "PUB_InvcHead.CustNum, PUB_InvcHead.InvoiceDate, Format([InvoiceDate]," & Chr(34) & invdat & Chr(34) & ") AS InvDatEDI, " & _
    "PUB_InvcHead.InvoiceType, PUB_InvcHead.InvoiceNum, PUB_InvcHead.InvoiceAmt, PUB_InvcDtl.InvoiceLine, " & _
    "qrsOrderHedDtlInv.OrderNum, qrsOrderHedDtlInv.PONum, " & _
    "IIf([ItemNum] Is Not Null And [ItemNum]<>" & Chr(34) & Chr(34) & ",[ItemNum],1) AS ItNum, " & _
    "PUB_InvcDtl.PartNum, PUB_InvcDtl.ShipDate, PUB_InvcDtl.PackNum, PUB_InvcDtl.OurShipQty, " & _
    "PUB_InvcDtl.UnitPrice, NZ([XPartNum]," & Chr(34) & Chr(34) & ") AS CustPNum, PUB_InvcDtl.ExtPrice, PUB_InvcDtl.PackLine, tblCust.EDICode " & _
    "FROM ((PUB_InvcHead INNER JOIN PUB_InvcDtl ON PUB_InvcHead.InvoiceNum = PUB_InvcDtl.InvoiceNum) " & _
    "LEFT JOIN qrsOrderHedDtlInv ON (PUB_InvcDtl.OrderNum = qrsOrderHedDtlInv.OrderNum) AND " & _
    "(PUB_InvcDtl.OrderLine = qrsOrderHedDtlInv.OrderLine)) INNER JOIN tblCust ON PUB_InvcHead.CustNum = tblCust.CustNum " & _
    "WHERE (((PUB_InvcHead.CustNum) =" & [Forms]![frmInvEntry]![cmbCustNum] & ") And " & _
    "((PUB_InvcHead.InvoiceDate) =#" & [Forms]![frmInvEntry]![txtInvDate] & "#) And " & _
    "((PUB_InvcHead.InvoiceType) = " & Chr(34) & SType & Chr(34) & ") And " & _
    "((tblCust.EDICode) Is Not Null And (tblCust.EDICode) <> " & Chr(34) & Chr(34) & ")) " & _
    "ORDER BY PUB_InvcHead.InvoiceNum, PUB_InvcDtl.InvoiceLine;"

Open in new window

0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 33772721
<<Also I did create the query graphically first. The way I have the last INNER JOIN is the way it is when I switch the graphical query to SQL. Is there anything else I can try? >>
And if you take the statement that is giving you an error and past it into SQL view and attempt to switch to graphical view it does not give you an error and runs fine?
I don't understand why your getting a syntax error then...
JimD.
0
 

Author Closing Comment

by:StampIT
ID: 33773258
JimD,
   Not sure what was missing initially but I did as you suggested and pasted the SQL statement into  SQL view. I received the same error. So I copied only the FROM to before the WHERE part of the statement that worked in the SQL view and pasted this into the SQL statement in the code and it worked. Thanks.
0
 
LVL 57
ID: 33773364
Probably a )  and that's why I came back and I asked again; those are HARD to spot.
I often go back and forth with the designer until I figure out where the problem is.
JimD.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pass Variables from Vba access to SQL Query 1 30
Mimic UNC drive 10 46
Filter Date on Split Form 8 30
Access - Question on concatenation of field values 3 29
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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 …

685 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