Where to put the brackets?

[rbinder]

Greetings all,

Continuing to work on my problem and I am now getting and error of '3075' - syntax error (missing operator) in query expression

the error line is

strSQL = strSQL & "HAVING (tbl_data_DispatchDetails.TradingName) = " & varVal2 & " AND (tbl_Data_DispatchLineitems.WineNumber) = " & VarVal0


(I have to admit query language is not my strength)

Thanks in advance.

rbinder

 

[Pat Hartman]

You probably need quotes around trading name and you need an ending semi-colon.

To debug string SQL, print out the string in the debug window. It is easier to see where things are missing once you see the evaluated expression. You can also copy it and paste it into the SQL view in the query builder to test it.

 

[WayneRyan]

r,

In VBA you're constructing a string to pass to the SQL parser:

Like --> Having TradingName = 'Some Company' <-- single-quote delimiter

strSQL = strSQL & "HAVING (tbl_data_DispatchDetails.TradingName) = " & varVal2 & " AND (tbl_Data_DispatchLineitems.WineNumber) = " & VarVal0

translates to

...HAVING (tbl_data_DispatchDetails.TradingName) = Some Company AND ...

The parser does all right seeing that TradingName must be equal to something.

When it reads the "Some" and finds "Company"; there's no operator between those
TWO things.

To treat varval2 as ONE thing:

strSQL = strSQL & "HAVING (tbl_data_DispatchDetails.TradingName) = '" & varVal2 & "' AND " & _
                  "       (tbl_Data_DispatchLineitems.WineNumber) = " & VarVal0 

That'll translate to:

HAVING (tbl_data_DispatchDetails.TradingName) = 'Some Company' AND 
       (tbl_Data_DispatchLineitems.WineNumber) = 12 <-- This doesn't need quotes
                                                        No embedded spaces

Wayne

 

[iago18]

Also, watch for how your SQL begins...

You wrote >>> strSQL = strSQL & "HAVING ...

You might want >>> strSQL = strSQL & " HAVING ...

Notice the extra space between the quotation and the word 'HAVING'. If you are combining the contents of strSQL with this new text you might need a space between them depending on what the contents of strSQL previously held.