Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why am I getting a Compile error on my SQL statement?
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","Fil led") " & "AS Fill_Status " & _ "FROM Rejections RIGHT JOIN Orders ON Rejections.Order=Orders.Order " & _ "WHERE Orders.Date Now() - 42 " & _ "ORDER BY Orders.Order, Rejections.Order;" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why am I getting a Compile error on my SQL statement?
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;" " wrote: 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","Fil led") " & "AS Fill_Status " & _ "FROM Rejections RIGHT JOIN Orders ON Rejections.Order=Orders.Order " & _ "WHERE Orders.Date Now() - 42 " & _ "ORDER BY Orders.Order, Rejections.Order;" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why am I getting a Compile error on my SQL statement?
On Oct 31, 11:12 am, Joel wrote:
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;" " wrote: 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","Fil led") " & "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"",""F illed"") AS Fill_Status " & _ then I believe that problem will go away. Please let me know if you still have problems. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Micrsoft Visual Basic Compile error: Expected: end of statement | Excel Discussion (Misc queries) | |||
VBAProject name compile error, not defined at compile time | Excel Programming | |||
error message: compile error, argument not optional | Excel Programming | |||
How do I get rid of "Compile error in hidden module" error message | Excel Discussion (Misc queries) |