Join expression not supported

[jquickuk]

Could you guys take a look at this query please. Doing ym head in now.
posts say it is down to the brackets not being the wrong place but i'm sure i've tried every way possible!!

I keep getting join expression not support. I also know that the whole syntax could be wrong but someone can help with the joins then i'll crack the rest.

SQLrefresh = "SELECT [Otto Item Prices].PRPROD AS [Debs Item] " & vbCrLf & _
"FROM (([All days] LEFT JOIN [Otto Item Prices] ON [All days].iteOpt = [Otto Item Prices].Otto_Item) INNER JOIN INTERFACE_SVWSODDP ON [Otto Item Prices].PRPROD = INTERFACE_SVWSODDP.SDPROD) " & vbCrLf & _
"WHERE ((([Otto Item Prices].ATP)>=1) AND (([All days].Status)=""OS"" Or ([All days].Status)=""WN"") AND (([All days].Cancel_Date)= date() AND ((INTERFACE_SVWSODDP.SDFEEDTIME)<Date()));"

Thanks in advance!

 

[RuralGuy]

Start by taking out the & vbCrLf's. If you want to make it more readable then use a line continuation sequence. " & _

 

[jquickuk]

Start by taking out the & vbCrLf's. If you want to make it more readable then use a line continuation sequence. " & _

Done!
Not sure where I got them from. Haven't used them anywhere else.

 

[RuralGuy]

Did that resolve the issue?

 

[jquickuk]

Oh sorry
No

 

[RuralGuy]

Have you tried pasting the SQL code into the SQL view of the Query Builder? It has helped me solve SQL issues before.

 

[boblarson]

Have you tried pasting the SQL code into the SQL view of the Query Builder? It has helped me solve SQL issues before.

Actually, I tried doing that with this particular SQL string and got that error message even before it told me it couldn't find the tables (because they didn't exist in my database). So, my suggestion is to go to the QBE grid and build your query from scratch there and then go to the SQL view and copy it and use it in your code. You will need to add the continuations and and change the " around the items that are criteria to single quotes or use a Chr(34) concatenated in. Actually, now that I think about it, that could be part of your problem right now. Try changing this:

AND (([All days].Status)=""OS"" Or ([All days].Status)=""WN"") AND (([All days].Cancel_Date)= date() AND ((INTERFACE_SVWSODDP.SDFEEDTIME)<Date()));"
To this:

AND (([All days].Status)=" & Chr(34) & OS & Chr(34) & " Or ([All days].Status)=" & Chr(34) & WN & Chr(34) & ") AND (([All days].Cancel_Date)= #" & date() & "# AND ((INTERFACE_SVWSODDP.SDFEEDTIME)< #" & Date() & "#));"

 

[jquickuk]

I took this sql from the query builder and most prob why it look naff.

AND (([All days].Status)=" & Chr(34) & OS & Chr(34) & " Or ([All days].Status)=" & Chr(34) & WN & Chr(34) & ") AND (([All days].Cancel_Date)= #" & date() & "# AND ((INTERFACE_SVWSODDP.SDFEEDTIME)< #" & Date() & "#));"

Thanks for this but still same error

 

[jquickuk]

Just one thing i have noticed.

This query used to call another query and i think that was to create the join. does that make sense?

I'm trying to do it all in one.

Is it possible to create to seprate queries in VB just like it would be in the query builder.

This is the SQL from the SQL view in the query builder.

The query I run

SELECT [_OS_WN_Cancellations - Used for Kathleen's daily extracts].[Debs Item]
FROM INTERFACE_SVWSODDP INNER JOIN [_OS_WN_Cancellations] ON INTERFACE_SVWSODDP.SDPROD = [_OS_WN_Cancellations].[Debs Item]
WHERE ((([_OS_WN_Cancellations].ATP)>=1) AND ((INTERFACE_SVWSODDP.SDFEEDTIME)<Date()))
ORDER BY [_OS_WN_Cancellations].ATP DESC;

_OS_WN_Cancellations Query

SELECT [All days].ID, [All days].[Cust no], [All days].[Ord no], [All days].Seq, [All days].iteOpt, [Otto Item Prices].Otto_Item, [Otto Item Prices].PRPROD AS [Debs Item], [Otto Item Prices].IPSTORE, [Otto Item Prices].[Current Price], [Otto Item Prices].ATP, [All days].Status, [All days].Qty, [All days].Cancel_Date, [All days].Time, [All days].Field9, [Qty]*[Otto Item Prices]![Current Price] AS Line_Cancel_Value
FROM [All days] LEFT JOIN [Otto Item Prices] ON [All days].iteOpt = [Otto Item Prices].Otto_Item
WHERE ((([All days].Status)="OS" Or ([All days].Status)="WN") AND (([All days].Cancel_Date)=[Cancel_Date dd/mm/yyyy]));