Trying to populate table with date limits 1

[BusMgr]

I am trying to populate a temporary table with sales data. I want to be able to populate with three months of data at a time. The code below does not error out, but it does not appear to see the dates in the strSQL statement. If I leave that line out of the strSQL statement, it populates okay. Do I need to enclose the vardate1 and vardate2 in single quotes in the strSQL statement?

Dim stDocName As String
Dim vardate1 As Variant
Dim vardate2 As Variant
Dim strSQL As String

strSQL = "Delete * from tblTmp3MthSales"
DoCmd.RunSQL strSQL

'If Forms![frmMonthlyTotals]![frameSalesAvg] = "12 " Then
vardate1 = DateSerial(Year(Date)-1, 12, 1)
Debug.Print vardate1
vardate2 = DateSerial(Year(Date), 2, 28)
Debug.Print vardate2

strSQL = "INSERT INTO tblTmp3MthSales ( PartNo, OrderQty )" _
& " SELECT [_CUST_ORDER_LINE].PART_ID, Sum([_RECEIVABLE_LINE].QTY) AS SumOfQTY " _
& " FROM ((_CUST_ORDER_LINE INNER JOIN _CUSTOMER_ORDER ON [_CUST_ORDER_LINE].CUST_ORDER_ID = [_CUSTOMER_ORDER].ID) " _
& " LEFT JOIN _PART ON [_CUST_ORDER_LINE].PART_ID = [_PART].ID) INNER JOIN (_RECEIVABLE INNER JOIN _RECEIVABLE_LINE ON " _
& " [_RECEIVABLE].INVOICE_ID = [_RECEIVABLE_LINE].INVOICE_ID) ON [_CUST_ORDER_LINE].CUST_ORDER_ID = [_RECEIVABLE_LINE].CUST_ORDER_ID " _
& " WHERE ((([_PART].Description) <> 'FREIGHT'))" _
& " AND ((([_PART].PRODUCT_CODE) = 'HORIZONTAL BITS') Or (([_PART].PRODUCT_CODE) = 'ROCK BITS'))" _
& " AND ((([_RECEIVABLE].INVOICE_DATE) BETWEEN " & vardate1 & " AND " & vardate2 & "))" _
& " GROUP BY [_CUST_ORDER_LINE].PART_ID HAVING (((Sum([_RECEIVABLE_LINE].QTY))>0))"

Debug.Print strSQL

DoCmd.RunSQL strSQL

The immediate window returns as the strSQL statement -
INSERT INTO tblTmp3MthSales ( PartNo, OrderQty ) SELECT [_CUST_ORDER_LINE].PART_ID, Sum([_RECEIVABLE_LINE].QTY) AS SumOfQTY FROM ((_CUST_ORDER_LINE INNER JOIN _CUSTOMER_ORDER ON [_CUST_ORDER_LINE].CUST_ORDER_ID = [_CUSTOMER_ORDER].ID) LEFT JOIN _PART ON [_CUST_ORDER_LINE].PART_ID = [_PART].ID) INNER JOIN (_RECEIVABLE INNER JOIN _RECEIVABLE_LINE ON [_RECEIVABLE].INVOICE_ID = [_RECEIVABLE_LINE].INVOICE_ID) ON [_CUST_ORDER_LINE].CUST_ORDER_ID = [_RECEIVABLE_LINE].CUST_ORDER_ID WHERE ((([_PART].Description) <> 'FREIGHT')) AND ((([_PART].PRODUCT_CODE) = 'HORIZONTAL BITS') Or (([_PART].PRODUCT_CODE) = 'ROCK BITS')) AND ((([_RECEIVABLE].INVOICE_DATE) BETWEEN 12/1/2003 AND 2/28/2004)) GROUP BY [_CUST_ORDER_LINE].PART_ID HAVING (((Sum([_RECEIVABLE_LINE].QTY))>0))

Thanks for your help in advance.

BusMgr

 

[Hmadyson]

Put # around your dates