Runtime Error 3075, Syntax Error (Missing Operator) 1

[Davidmc555]

Hi. I've had a quick look around to find the answer to the problem I'm having but with no luck.

I'm writing a VBA app for my company over a Access DB.

I've done plenty of queries, but this one is being a pain.

==========================================================

(VBA Code)

Set dbMain = CurrentDb

DoCmd.SetWarnings False

sqlPack = "SELECT TOP 1 CustID, Ref FROM Camden INNER JOIN CamdenAccess " & _
"ON Camden.[Order] = CamdenAccess.[Order] INNER JOIN " & _
"AccessoryPackItems ON CamdenAccess.AccessID = AccessoryPackItems.AccessID " & _
"WHERE AccessoryPackItems.PackageID = " & lngCurrentPackage

Set rsPackage = dbMain.OpenRecordset(sqlPack, dbOpenDynaset, dbSeeChanges)

lngAPCustID = rsPackage.Fields("CustID")
strAPRef = rsPackage.Fields("Ref")

sqlAccess = "SELECT CustID, Ref FROM Camden INNER JOIN CamdenAccess ON Camden.[Order] = " & _
"CamdenAccess.[Order] WHERE CamdenAccess.AccessID = " & lngAccessID

Set rsAccess = dbMain.OpenRecordset(sqlAccess, dbOpenDynaset, dbSeeChanges)

lngCACustID = rsAccess.Fields("CustID")
strCARef = rsAccess.Fields("Ref")

===========================================================

Just to clear up, sqlPack is a string value, lngCurrentPackage is a long. Basically anything with lng at the begining of the name is a long and str is a string.

I've tried various round brackets and double quotes but I still get the following error.

"Run-time error '3075':

Syntax error (missing operator) in query expression 'Camden.[Order] = CamdenAccess.[Order] INNER JOIN AccessoryPackItems ON CamdenAccess.AccessID = AccessoryPackItems.AccessID'."

Just before I finish the post, while in debug mode, I stepped over the first query and the second query ran fine and returned to the recordset.

Can anyone in their infinite wisdom see what I doing wrong?

Thanks in advance.

 

[PHV]

And this ?
sqlPack = "SELECT TOP 1 CustID, Ref FROM Camden INNER JOIN " & _
"(CamdenAccess INNER JOIN AccessoryPackItems ON CamdenAccess.AccessID = AccessoryPackItems.AccessID " & _
") ON Camden.Order = CamdenAccess.Order " & _
"WHERE AccessoryPackItems.PackageID = " & lngCurrentPackage

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xlbo]

I would suggest that this would better fit in the SQL query forum for Access: forum701

BUT it is perfectly valid here (there's just more SQL experts there)

Can't see anything wrong to my relatively untrained eye but my most common error is missing out the space before FROM and WHERE. You might just wanna check them in the 1st instance....

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Davidmc555]

@PHV

My friend, you are what we call here, a legend. That worked perfectly, thanks for your help.

@xlbo

I looked around at different forums (only signed up today) and felt it may be best suited here, sorry if it's not. As a member of a PC gaming forum where posts can end up anywhere, I can understand how frustrating when things don't get placed where they should be.

Thanks again everyone!

 

[xlbo]

David - no apologies necessary - this is a perfectly valid place to ask the question - just wanted to give you the option of somewhere else that may be better placed to answer. Course, I didn't count on PHV !!

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question