Syntax Error in a SQL string

  • Thread starter Thread starter Paulo
  • Start date Start date
P

Paulo

Can someone spot the syntax error in this SQL string? I tried, but could not
see where it is.

Thanks in advance,

Paulo

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Set qdfTemp = CurrentDb.CreateQueryDef("Current Orders Set", _
"SELECT IIf([Orders]![Status]='REC',[Orders]![DC Cims Week
Actual],[Orders]![ETA to DC Cims Week]) " & _
"AS RelevantWeek, Sum(Orders.[Order Qty]) AS [SumOfOrder Qty],
Sum(Orders.[Received Qty]) AS [SumOfReceived Qty], " & _

"Sum(IIf([Orders]![Channel]='WHOLESALE',IIf([Orders]![Status]='REC',[Orders]![Received Qty]*[WSale Price], " & _
"[Orders]![Order Qty]*[WSale
Price]),IIf([Orders]![Status]='REC',[Orders]![Received Qty]*[Std Retail
Price], " & _
"[Orders]![Order Qty]*[Std Retail Price]) AS Revenue,
Sum(IIf([Orders]![Status]='REC', " & _
"[Orders]![Received Qty]*[Landed GBP Cost],[Orders]![Order
Qty]*[Landed GBP Cost])) AS Costs, " & _
"Orders.[Col Ssn], Orders.Currency, Orders.Channel, Orders.Supplier
FROM Orders WHERE" & _
strWhere)
 
First error is that you cannot use the bang (!) operator with fields in
tables. For instance, the first field needs to be

IIf([Orders].[Status]='REC',[Orders].[DC Cims Week Actual],[Orders].[ETA to
DC Cims Week])

I stopped looking after that...
 
It's a lot easier to debug this problem is you do something like

Dim strSQL as String

strSQL ="SELECT IIf([Orders]![Status]='REC',[Orders]![DC Cims Week
Actual],[Orders]![ETA to DC Cims Week]) " & _
"AS RelevantWeek, Sum(Orders.[Order Qty]) AS [SumOfOrder Qty],
Sum(Orders.[Received Qty]) AS [SumOfReceived Qty], " & _

"Sum(IIf([Orders]![Channel]='WHOLESALE',IIf([Orders]![Status]='REC',[Orders]![Received
Qty]*[WSale Price], " & _
"[Orders]![Order Qty]*[WSale
Price]),IIf([Orders]![Status]='REC',[Orders]![Received Qty]*[Std Retail
Price], " & _
"[Orders]![Order Qty]*[Std Retail Price]) AS Revenue,
Sum(IIf([Orders]![Status]='REC', " & _
"[Orders]![Received Qty]*[Landed GBP Cost],[Orders]![Order
Qty]*[Landed GBP Cost])) AS Costs, " & _
"Orders.[Col Ssn], Orders.Currency, Orders.Channel, Orders.Supplier
FROM Orders WHERE" & _
strWhere

Debug.Print strSQL

Now you can see exactly what the string you have generated looks like. And
even copy and paste it into a new query to execute and see what might be wrong
with the query. Also, the syntax error could be in strWhere. Perhaps all you
need is to add a space after "WHERE" so you don't end up with
WHERESomeField = ... when you want WHERE SomeField = ....

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
I'm not an expert with this stuff (yet) but if you are converting SQL to VBA,
look at this:
http://allenbrowne.com/ser-71.html

I used it recently and had great success with it!!

Thanks Allen!!
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


John Spencer said:
It's a lot easier to debug this problem is you do something like

Dim strSQL as String

strSQL ="SELECT IIf([Orders]![Status]='REC',[Orders]![DC Cims Week
Actual],[Orders]![ETA to DC Cims Week]) " & _
"AS RelevantWeek, Sum(Orders.[Order Qty]) AS [SumOfOrder Qty],
Sum(Orders.[Received Qty]) AS [SumOfReceived Qty], " & _

"Sum(IIf([Orders]![Channel]='WHOLESALE',IIf([Orders]![Status]='REC',[Orders]![Received
Qty]*[WSale Price], " & _
"[Orders]![Order Qty]*[WSale
Price]),IIf([Orders]![Status]='REC',[Orders]![Received Qty]*[Std Retail
Price], " & _
"[Orders]![Order Qty]*[Std Retail Price]) AS Revenue,
Sum(IIf([Orders]![Status]='REC', " & _
"[Orders]![Received Qty]*[Landed GBP Cost],[Orders]![Order
Qty]*[Landed GBP Cost])) AS Costs, " & _
"Orders.[Col Ssn], Orders.Currency, Orders.Channel, Orders.Supplier
FROM Orders WHERE" & _
strWhere

Debug.Print strSQL

Now you can see exactly what the string you have generated looks like. And
even copy and paste it into a new query to execute and see what might be wrong
with the query. Also, the syntax error could be in strWhere. Perhaps all you
need is to add a space after "WHERE" so you don't end up with
WHERESomeField = ... when you want WHERE SomeField = ....

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Can someone spot the syntax error in this SQL string? I tried, but could not
see where it is.

Thanks in advance
Click to expand...
.
Click to expand...
 
Back
Top