Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    4

    Question 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.

    Thanks for any suggestions!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could recreate the query each time you need it:
    Code:
    Function CreateQuery()
    
        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
            End If
        End If
    
        ' Create query.
        Set qdf = CurrentDb.CreateQueryDef(strQueryName, strSQL)
    
    End Function
    Have a nice day!

  3. #3
    Join Date
    Aug 2009
    Posts
    4

    Need to pass dates for query for report

    I actually need the dates passed to the Query for several reports... so it's important for these parameters to pass specifically to the query already created (rather than creating anew each time).

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Having reports based on a query does not prevent this one from being re-created each time you need it.

    Whatever, what's the actual code of your query now ?

    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •