How to use a calendar form or object to input criteria into a query?

[Triacona]

Hi everyone

I wanted to know how to use a calendar form or object to input criteria into a query?
I'm running a query/report and the user has to enter a start date and a finish date.
I have two calendar forms - Calendar and Calendar2 (do I need to calendar objects to do a start and finish date with?)
So in the query I put in the following criteria:

Between [Forms]![Calendar]![Calendar0] And [Forms]![Calendar2]![Calendar0]

and in the calendar update code I put the following code:

Dim stLinkCriteria As String
Dim stDocName As String
stDocName = "DateValiDateDecPsCd"

Set rs = Me.Recordset.Clone
rs.FindFirst "[DATEDECISS] = '" & Me![Calendar0] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
DoCmd.OpenReport stDocName, acViewPreview, stLinkCriteria = rs.EOF


The above code I use for both calendar controls.

I am trying to input the Decision Date (DATEDECISS) via the calendar controls (between to specified dates) hence the Between [Forms]![Calendar]![Calendar0] And [Forms]![Calendar2]![Calendar0]

Please help..
any help would be greatly appreciated.
Kind regards
Triacona

 

[dhookom]

If you have the criteria in your report's Record Source, you shouldn't have to do any filtering in the OpenReport method.

If you only want to filter the report on the DATEDECISS field based on the values of the calendar controls in the current form, I would remove the criteria from the query and use code like:

    Dim stLinkCriteria As String
    Dim stDocName As String
    stDocName = "DateValiDateDecPsCd"
    stLinkCriteria = "1=1 "
    If Not IsNull(Me.Calendar0) Then
        stLinkCriteria = stLinkCriteria & " AND DATEDECISS>=#" & _
            Me.Me.Calendar0 & "# "
    End If
    If Not IsNull(Me.Calendar1) Then
        stLinkCriteria = stLinkCriteria & " AND DATEDECISS<=#" & _
            Me.Me.Calendar1 & "# "
    End If
    DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Thank you very much for you help. I managed to find a helpful link:

http://office.microsoft.com/en-us/access/HA100963141033.aspx

You create a form with two "DateCalControl.DateWithCalendar" controls and rename each respectively to startdate and enddate.

You can add the calendar controls via clicking the "More Tools" button on the toolbar and then selecting "DateCalControl.DateWithCalendar"

Then within the query in your date field, insert Between [Forms]![form name].[StartDate] And [Forms]![form name].[EndDate]

This will then pull through the dates from that form into the query.
Thanks again for all you help