Why am I getting a Compile error on my SQL statement?

[nouveauricheinvestments]

Any idea what is wrong with my src statement? It is giving me a
compile error when I attempt to run the code and it highlights the
"Rejected" portion. It runs perfectly fine as a query in the database
and I can't see any quotes or anything out of place...

Thanks.

Src = "SELECT Orders.Order, Orders.Date, Orders.Time,
Rejections.Order, Rejections.Date, " & _
"Rejections.Time, " &
"IIf(Orders.Order=Rejections.Order,"Rejected","Filled") " & "AS
Fill_Status " & _
"FROM Rejections RIGHT JOIN Orders ON
Rejections.Order=Orders.Order " & _
"WHERE Orders.Date > Now() - 42 " & _
"ORDER BY Orders.Order, Rejections.Order;"

 

[Joel]

You have extra double quotes in a few spots. Try this

Src = "SELECT Orders.Order, Orders.Date, Orders.Time," & _
"Rejections.Order, Rejections.Date, Rejections.Time, " & _
"IIf(Orders.Order=Rejections.Order,Rejected,Filled) " & _
"AS Fill_Status " & _
"FROM Rejections RIGHT JOIN Orders ON Rejections.Order=Orders.Order "
& _
"WHERE Orders.Date > Now() - 42 " & _
"ORDER BY Orders.Order, Rejections.Order;"

 

[nouveauricheinvestments]

You have extra double quotes in a few spots. Try this

Src = "SELECT Orders.Order, Orders.Date, Orders.Time," & _
"Rejections.Order, Rejections.Date, Rejections.Time, " & _
"IIf(Orders.Order=Rejections.Order,Rejected,Filled) " & _
"AS Fill_Status " & _
"FROM Rejections RIGHT JOIN Orders ON Rejections.Order=Orders.Order "
& _
"WHERE Orders.Date > Now() - 42 " & _
"ORDER BY Orders.Order, Rejections.Order;"

It actually turned out to be I needed two more quotes. This is the
explanation I got that saved the day...



At first glance I'd say it is the quoting issue (another common
thing). SQL says that if it is a string you need to quote it so if
you run the query in Access you put quotes around Rejected and Filled
and it works.

VB says please pass a SQL statement as a string which means you put
quotes around the entire statement...but wait you have quotes inside
the statement and that is the problem.

In your example when VB tries to run it sees the following

Src = "SELECT Orders.Order, Orders.Date, Orders.Time,
Rejections.Order, Rejections.Date, Rejections.Time,
IIf(Orders.Order=Rejections.Order,"

The quote you put in front of Rejected to start a string in SQL
actually ended the string in VBA so somehow we need to tell VBA that
we want the quote to be part of the string rather than ending the
string. This is generally called and escape character or sequence.
For example in C we would put /" and the / tells the compiler to treat
the " as part of the string (note that the / is taken out of the final
string). In this circumstance in VB it is another quote character
right next to the one you want to keep.

So if you change the 2nd line to:

"Rejections.Time,
IIf(Orders.Order=Rejections.Order,""Rejected"",""Filled"") AS
Fill_Status " & _

then I believe that problem will go away.

Please let me know if you still have problems.