Creating a SQL string in VBA that uses criteria from a form

[mhoctober]

I need to create code that creates a recordset as a report runs. The recordset is based on an SQL string that has criteria (a date) appended to the end of it from a value that sits in a txt box on a form.



This is the string thus far...

Set rs1 = db.OpenRecordset("SELECT tblOrders.orderDeliveryDate, tblOrders.orderDropNumber, Count(tblOrders.orderDropNumber) AS CountOforderDropNumber FROM tblContacts INNER JOIN tblOrders ON tblContacts.contactID = tblOrders.contactID GROUP BY tblOrders.orderDeliveryDate, tblOrders.orderDropNumber Where tblOrders.orderDeliveryDate=[forms]![frmCollectDates].[txtStartDate]")

Could anyone help me with the bit at the end of the string as I keep getting the compile error missing list seperator or ) error message.

Ive been trying for a while with a variety of changes to it but no luck!

[orange]

Set rs1 = db.OpenRecordset("SELECT tblOrders.orderDeliveryDate,  tblOrders.orderDropNumber, 
Count(tblOrders.orderDropNumber) AS  CountOforderDropNumber FROM tblContacts INNER JOIN tblOrders ON  
tblContacts.contactID = tblOrders.contactID GROUP BY  tblOrders.orderDeliveryDate, tblOrders.orderDropNumber 
Where  tblOrders.orderDeliveryDate=[forms]![frmCollectDates].[txtStartDate]")

I think the issue is with the value from Forms.... The value is within your OpenRecordSet string. It looks like some constant to Access.
You have to let the [forms]![frmCollectDates].[txtStartDate] get evaluated and then appended to the OpenRecordSet string.
Something like this (UNTESTED)

Set rs1 = db.OpenRecordset("SELECT tblOrders.orderDeliveryDate,  tblOrders.orderDropNumber, 
Count(tblOrders.orderDropNumber) AS  CountOforderDropNumber FROM tblContacts INNER JOIN tblOrders ON 
 tblContacts.contactID = tblOrders.contactID GROUP BY  tblOrders.orderDeliveryDate, tblOrders.orderDropNumber Where
  tblOrders.orderDeliveryDate= #" &[forms]![frmCollectDates].[txtStartDate] & "#")

You may have to tweak the statement depending on txtStartDate definition.
Access usually wants Dates delimited with #, but if your txtStartDate is string, you may need to replace # with single quote,
or you may need to use the CDate() function.