Create A report between two dates

[Loopyl00]

I have managed to create a Report from a form that displays data per date

In the query criteria for the date it references the form

[Forms]![equipe_form]![Date de Vente]

The report is based on the results of the query as per the information entered in the form

so far so good

However, I want the user to be able to select a date range in the form - what do i enter in the query and how do i reference these to fields in the form

 

[dhookom]

I rarely us references to controls on forms in queries that are record sources. Consider using code like:

Dim strWhere As String
Dim strReport as String
strWhere = "1=1 "
strReport = "rptYourReportNameHere"
If not IsNull(Me.txtStartDate) Then
    strWhere = strWhere & " AND [DateField]>=#" & _
        Me.txtStartDate & "# "
End If
If not IsNull(Me.txtEndDate) Then
    strWhere = strWhere & " AND [DateField]<=#" & _
        Me.txtEndDate & "# "
End If
DoCmd.OpenReport strReport, acPrintPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[Loopyl00]

Thanks for such a quick reply

Does this enable me to enter the date range in the form? the code seems to reference a report

I wanted to have a form where the user selects a date range from a query

 

[Loopyl00]

I was hoping to add two boxes to the form StartDate EndDate and then have criteria in the query Date field

I don't know what this criteria should be

 

[dhookom]

I provided sample code that might be behind a command button that will use the values of two text boxes to limit a date range in a report. I assumed that was what you expected.

Duane
Hook'D on Access
MS Access MVP

 

[Loopyl00]

Thanks - I actually managed to create an unbound text box for StartDate and EndDate on the Form then in the Criteria for the query I put

Between [Forms]![Sommaire_Vendeur_Date]![Startdate] And [Forms]![Sommaire_Vendeur_Date]![enddate]

This now runs the query from a form when two dates are entered

 

[TheAceMan1]

How are ya Loopyl00 . . .

With a slight modification of the code provided by [blue]dhookom[/blue] the report can show:
[ol][li]Dates [blue]>=[/blue] txtStartDate (Input StartDate Only).[/li]
[li]Dates [blue]<=[/blue] txtEndDate (Input EndDate Only).[/li]
[li]Dates within the input start/end range.[/li]
[li]All dates (no date inputs).[/li][/ol]

[blue]   Dim Cri As String, rptName As String
   
   rptName = "rptYourReportNameHere"
   
   If Not IsNull(Me.txtStartDate) Then
      Cri = "[DateField]>= #" & Me.txtStartDate & "#"
   End If
   
   If Not IsNull(Me.txtEndDate) Then
      If Cri <> "" Then
         Cri = Cri & " AND [DateField]<= #" & Me.txtEndDate & "#"
      Else
         Cri = "[DateField]<= #" & Me.txtEndDate & "#"
      End If
   End If
   
   DoCmd.OpenReport rptName, acPrintPreview, , Cri[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

TheAceman1,
I try to avoid having to repeat the:

    If Cri <> "" Then

by using

    strWhere = "1=1 "

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Howdy dhookom . . .

Didn't notice until after submission

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]