Syntax Error

[Jimtheessexboy]

I'm running a VBA query in excel trying to import the field from a table, simple so I thought, any idea on why i'm getting syntax error codes on this part?

Application.StatusBar = "Retrieving Depot Names"
sSQL = "SELECT tbldepot.DepotName 'Depot' " & vbCr & _
"FROM tbldepot, " & vbCr & _
"GROUP BY tbldepot.DepotName "
getSQLintoRange sSQL, Cells(1, iCol), True, True
iCol = iCol + 2


same thing on

'-= DFE =-
'Some manual DFEs have auth code but left at created
'AAP children have the DFE, not parent
Application.StatusBar = "Retrieving DFE Values"
sSQL = "SELECT tbldepot.DepotName 'Depot', " & vbCr & _
" SUM (di.Qty * di.Rate) 'DFEVal' " & vbCr & _
"FROM tblA537 a, tblA537DFE d, tblA537DFEItem di, tblDepot " & vbCr & _
"WHERE tblDepot.DepotID = a.DepotID " & vbCr & _
"AND a.OrderNumber = d.OrderNumber AND d.DFEID = di.DFEID " & vbCr & _
"AND NOT d.Authorisation IS NULL AND d.DFEStatus<>4 " & vbCr & _
"GROUP BY tblDepot.DepotName " & vbCr & _
"ORDER tblDepot.DepotName "
getSQLintoRange sSQL, Cells(1, iCol), True, True
iCol = iCol + 2

 

[DrSnuggles]

Might be wrong here (don't do alot of excel VBA), but should it be:

SELECT tbldepot.DepotName as [Depot] " & vbCr & _

 

[Jimtheessexboy]

Thanks DR Snuggles but I think it might be the "FROM" statement. I did change the code to what you suggested but no joy. Does anyone know the rules on a SELECT statement?

 

[DrSnuggles]

I've noticed another thing.
You only need commas to seperate multiple field names.
You have a comma after the FROM table statement.
Remove this too.

ie. SELECT field1, field2 FROM table WHERE CLAUSE.

Depending on the SQL you are using, you may need a ';' at the end of your statement too.

 

[Jimtheessexboy]

Thanks Doc, first part now runs fine, any ideas on the second. Probably another comma issue!

 

[DrSnuggles]

A great way of testing your SQL is against your db.
Thus:
Whilst in Debug mode, capture the value of sSQL.
Stick this into a new SQL window in the database that you are retrieving data from. Then view by 'Design View' and check that everything looks Okay.
Then run it. The error message you get should indicate where the problem is.

Hope this helps.