Error in SQL Where Statement

[jfgambit]

Hello All:

I've drawn a blank and for some reason can't get my code for exporting out records to multiple excel sheets to work. The code is as follows:

Dim MyRS As Recordset
Dim SID As String
Dim MyQD As QueryDef
Dim StDt As Date
Dim EdDt As Date
Dim MyStr As Variant

SetWarnings = False

StDt = InputBox("What is Start Date?", "Commission Export Start Date")
EdDt = InputBox("What is End Date?", "Commission Export End Date")

Set MyRS = CurrentDb.OpenRecordset("SELECT tblSAPBookings.[Sales Rep] FROM tblSAPBookings WHERE (((tblSAPBookings.OrderDate) Between " & StDt & " AND " & EdDt & ")) GROUP BY tblSAPBookings.[Sales Rep];")

Set MyQD = CurrentDb.OpenRecordset("SELECT tblSAPBookings.[Sales Rep], tblSAPBookings.[Sales ID], tblSAPBookings.[End User], tblSAPBookings.[End Name], tblSAPBookings.Qty, tblSAPBookings.[Unit Price], tblSAPBookings.[EXT Price], tblSAPBookings.[ZIP Code], tblSAPBookings.State, tblSAPBookings.Country, tblSAPBookings.[Order #], tblSAPBookings.[PO #], tblSAPBookings.[Part #], tblSAPBookings.Desc, tblProductHeirarchy.ProdCommCatagory, tblSAPBookings.OrderDate, tblSAPBookings.PCR, tblSAPBookings.CommPayment " & vbNewLine _
& "FROM tblSAPBookings LEFT JOIN tblProductHeirarchy ON tblSAPBookings.[Prod Hier] = tblProductHeirarchy.ProductHierarchy " & vbNewLine _
& "GROUP BY tblSAPBookings.[Sales Rep], tblSAPBookings.[Sales ID], tblSAPBookings.[End User], tblSAPBookings.[End Name], tblSAPBookings.Qty, tblSAPBookings.[Unit Price], tblSAPBookings.[EXT Price], tblSAPBookings.[ZIP Code], tblSAPBookings.State, tblSAPBookings.Country, tblSAPBookings.[Order #], tblSAPBookings.[PO #], tblSAPBookings.[Part #], tblSAPBookings.Desc, tblProductHeirarchy.ProdCommCatagory, tblSAPBookings.OrderDate, tblSAPBookings.PCR, tblSAPBookings.CommPayment " & vbNewLine _
[B]& "WHERE (tblSAPBookings.OrderDate) Between #" & StDt & "# AND #" & EdDt & "#;")[/B]


'Move to first Sale ID
MyRS.MoveFirst
Do
SID = MyRS("Sales Rep")

'Select the Commission data based on the SaleId selected by MyRS
MyStr = MyQD
DoCmd.OutputTo acOutputQuery, MyStr, "MicrosoftExcel(*.xls)", "C:\Finance\Commssions\CommissionDocs\" & SID & Format(Date, "MM") & Format(Date, "YYYY") & ".xls"

'Move to Next record and Loop until EOF
MyRS.MoveNext
Loop Until MyRS.EOF = True
MyRS.Close
MyQD.Close

SetWarnings = True

My error keeps coming up in the bolded WHERE Statement. Can anyone see what I'm missing, I know it has got to be something simple.

 

[Ian Mac]

Hi,

You need to remove the last close bracket ')'

You also may want to make the code a little easier to read rather than it running into the PC to your right.

strSQL = ""
strSQL = strSQL & "SELECT tblSAPBookings.[Sales Rep], tblSAPBookings.[Sales ID], "
strSQL = strSQL & "tblSAPBookings.[End User], tblSAPBookings.[End Name], "
strSQL = strSQL & "tblSAPBookings.Qty, tblSAPBookings.[Unit Price], "
strSQL = strSQL & "tblSAPBookings.[EXT Price], tblSAPBookings.[ZIP Code], "
strSQL = strSQL & "tblSAPBookings.State, tblSAPBookings.Country, "
strSQL = strSQL & "tblSAPBookings.[Order #], tblSAPBookings.[PO #], "
strSQL = strSQL & "tblSAPBookings.[Part #], tblSAPBookings.Desc, "
strSQL = strSQL & "tblProductHeirarchy.ProdCommCatagory, "
strSQL = strSQL & "tblSAPBookings.OrderDate, tblSAPBookings.PCR, "
strSQL = strSQL & "tblSAPBookings.CommPayment "
strSQL = strSQL & "FROM tblSAPBookings "
strSQL = strSQL & "LEFT JOIN tblProductHeirarchy "
strSQL = strSQL & "ON tblSAPBookings.[Prod Hier] = tblProductHeirarchy.ProductHierarchy "
strSQL = strSQL & "GROUP BY tblSAPBookings.[Sales Rep], tblSAPBookings.[Sales ID], "
strSQL = strSQL & "tblSAPBookings.[End User], tblSAPBookings.[End Name], "
strSQL = strSQL & "tblSAPBookings.Qty, tblSAPBookings.[Unit Price], "
strSQL = strSQL & "tblSAPBookings.[EXT Price], tblSAPBookings.[ZIP Code], "
strSQL = strSQL & "tblSAPBookings.State, tblSAPBookings.Country, tblSAPBookings.[Order #], "
strSQL = strSQL & "tblSAPBookings.[PO #], tblSAPBookings.[Part #], "
strSQL = strSQL & "tblSAPBookings.Desc, tblProductHeirarchy.ProdCommCatagory, "
strSQL = strSQL & "tblSAPBookings.OrderDate, tblSAPBookings.PCR, "
strSQL = strSQL & "tblSAPBookings.CommPayment "
strSQL = strSQL & "WHERE (tblSAPBookings.OrderDate) Between #" & StDt & "# AND #" & EdDt & "#;"

Set MyQD = CurrentDb.OpenRecordset(strSQL)

Also add

Dim strSQL as String

to your declarations.

Also for what it's worth you should avoid spaces in your field headers, it's not good practice, hence why you have ['s round your field names.

Hope that helps,

 

[jfgambit]

Thanks Ian, I'll give it a go. And yes I know about the spaces, The database was handed to be from someone else and is connected via SQL tables that a previous individual built wiith Spaces in the names. An update project for another time I'm afraid.