Unanswered: Passing a Calendar Control value from a form to a Query Criteria?
Hi Folks - new to Access development (and programming in general!). I have two Calendar controls on a form for establishing "Start" and "End" dates. Upon button click on the same form I want to run a query (qryWeekSum) that takes these two calendar values and uses them to filter only records for a particular date range.
I set up an event that runs the query, and also declared date variables for the Start and End values. But now I'm stuck - not sure how to pass these values to the Query.
Sorry - know this is probably really basic stuff, but can't find a solution in my stack of books.
You could recreate the query each time you need it:
Dim dbs As Database
Dim qdf As QueryDef
Dim strQueryName as String
Dim strSQL as string
Dim StartDate as Variant
Dim StopDate as Variant
strQueryName = "... ' Name of the query.
StartDate = ... ' Some valid date.
StopDate = ... ' Some valid date .
strSQL = "SELECT ... FROM ... WHERE ( ... ) ' Some valid SQL sentence.
' Append the date interval clause.
' StopDate should possibly be StopDate + 1 (Date part only means Date at midnight ex. 01/01/2000 0:00:00).
strSQL = strSQL & " AND (([Some Date] >= #" & Format(StartDate, "mm/dd/yy")) & "# AND " & _
"([Some Date]<= #" & Format(StopDate, "mm/dd/yy") & "#))"
' Delete a former query with the same name if it already exists.
If Not IsNull(DLookup("Name", "MSysObjects", "Name = '" & QueryName & "' AND Type = " & CStr(5))) Then
DoCmd.DeleteObject acQuery, strQueryName
' Create query.
Set qdf = CurrentDb.CreateQueryDef(strQueryName, strSQL)