Passing date Parameters between Queries in Access

Can someone please tell me how I amend this code to accept date parameters?

Dim strSQL As String
Dim stDate As Date

DoCmd.RunSQL strSQL
strSQL = "SELECT Interface.RecType, Count(Interface.RecType) AS CountOfRecType," & _
"Sum(Interface.Amount) AS SumOfAmount, Interface.CreatedDate INTO test2 FROM Interface " & _
"GROUP BY Interface.RecType, Interface.CreatedDate, Interface.CreatedDate HAVING ((Interface.CreatedDate)= '#' &stDate& '#');"
DoCmd.RunSQL strSQL

I get Datatype mismatch when I try to run. I've a feeling my & # and variable definition combinations are wrong!

this works fine:

strSQL = "SELECT Interface.RecType, Count(Interface.RecType) AS CountOfRecType," & _
"Sum(Interface.Amount) AS SumOfAmount, Interface.CreatedDate INTO test FROM Interface " & _
"GROUP BY Interface.RecType, Interface.CreatedDate, Interface.CreatedDate HAVING ((Interface.CreatedDate)=#07/29/2004#);"

but I am trying to parameterise query so I can run multiple queries for the same date.

Thanks for your help.

Date is a Variant type, so you should implement the following:

Dim strSQL As String, varDate As Variant

varDate = Me!Date (... or whatever)

strSQL = "SELECT Interface.RecType, Count(Interface.RecType) AS CountOfRecType," & _
"Sum(Interface.Amount) AS SumOfAmount, Interface.CreatedDate INTO test FROM Interface " & _
"GROUP BY Interface.RecType, Interface.CreatedDate, Interface.CreatedDate HAVING (((Interface.CreatedDate)=#" & varDate & "#));"

Note the triple/double bracketing and the positions of the double quotes. If you want to play it safe, avoid using single quotes for cross platform compatibility and where a single quote in a data string will cause an error as in
'O'Malley'.

Also, it seems that you are using "Interface.CreatedDate" twice in your grouping.

Thank you - that works great.

Does anyone know how I would pass such a variable into a query that isn't defined within the VBA code itself?

I have tried to edit the SQL of a query to accept the Date variant as detailed above and call the query from the VBA but the query falls over.

ie:

This (myQuery):

SELECT Interface.RecType, Count(Interface.RecType) AS CountOfRecType, Sum(Interface.Amount) AS SumOfAmount, Interface.CreatedDate
FROM Interface
GROUP BY Interface.RecType, Interface.CreatedDate, Interface.CreatedDate
HAVING (((Interface.CreatedDate)=#" & varDate & "#));

falls over if I call the query by means of:

stDocName = "myQuery"
DoCmd.OpenQuery stDocName, acNormal, acEdit

because it doesn't like the syntax of the HAVING clause. Can anyone tell me how to achieve this?

Thank you