Basic Filtering For Data

[williamr]

I have a form where I ask 3 questions, who the report is for, the start date
and the stop date. I would like to write code to filter a report for that
data. Can someone show me how?

 

[Pieter Wijnen]

Create a Command Button (RunRep)
& Create this [Event Procedure], you can use the wizard to generate the
"raw" command if you like
note that I use military format for the dates, US will also work

Private Sub RunRep_Click()
Dim strLinkCriteria As String
strLinkCriteria = "MyToWhomFld='" & Me.ToWhomCtl.Value & "'" & _
" MydateFld BetWeen" &
Format(Me.StartDateCtl.Value, "\#yyyy-mm-dd\#") & _
" AND " & Format(Me.EndDateCtl.Value,
"\#yyyy-mm-dd\#")
DoCmd.OpenReoprt "MyRep", acViewPreview, WhereCondition:=strLinkCriteria
End Sub

HtH

Pieter

 

[williamr]

Pieter, Thanks for responding. I don't completely under. My 3 fields are
"txtFilterName", "DateStart", and "DateStop", what does Me.ToWhomCtl.value
represent and Me.EndDateCtl.Value represent?

Create a Command Button (RunRep)
& Create this [Event Procedure], you can use the wizard to generate the
"raw" command if you like
note that I use military format for the dates, US will also work

Private Sub RunRep_Click()
Dim strLinkCriteria As String
strLinkCriteria = "MyToWhomFld='" & Me.ToWhomCtl.Value & "'" & _
" MydateFld BetWeen" &
Format(Me.StartDateCtl.Value, "\#yyyy-mm-dd\#") & _
" AND " & Format(Me.EndDateCtl.Value,
"\#yyyy-mm-dd\#")
DoCmd.OpenReoprt "MyRep", acViewPreview, WhereCondition:=strLinkCriteria
End Sub

HtH

Pieter

I have a form where I ask 3 questions, who the report is for, the start
date
and the stop date. I would like to write code to filter a report for that
data. Can someone show me how?

 

[John W. Vinson]

I have a form where I ask 3 questions, who the report is for, the start date
and the stop date. I would like to write code to filter a report for that
data. Can someone show me how?

Base the Report on a Query referencing the form, using criteria such as

=[Forms]![NameOfForm]![txtWhoIsItFor]

and

= CDate([Forms]![NameOfForm]![txtStartdate] AND < DateAdd("d", 1, CDate([Forms]![NameOfForm]![txtEndDate]))

The DateAdd stuff is to ensure that dates with a time component will be
retrieved correctly.

John W. Vinson [MVP]

 

[Pieter Wijnen]

They represent the actual controls on your form, which may (should) differ
from yor table's field names

HtH

Pieter

Pieter, Thanks for responding. I don't completely under. My 3 fields are
"txtFilterName", "DateStart", and "DateStop", what does Me.ToWhomCtl.value
represent and Me.EndDateCtl.Value represent?

Create a Command Button (RunRep)
& Create this [Event Procedure], you can use the wizard to generate the
"raw" command if you like
note that I use military format for the dates, US will also work

Private Sub RunRep_Click()
Dim strLinkCriteria As String
strLinkCriteria = "MyToWhomFld='" & Me.ToWhomCtl.Value & "'" & _
" MydateFld BetWeen" &
Format(Me.StartDateCtl.Value, "\#yyyy-mm-dd\#") & _
" AND " & Format(Me.EndDateCtl.Value,
"\#yyyy-mm-dd\#")
DoCmd.OpenReoprt "MyRep", acViewPreview,
WhereCondition:=strLinkCriteria
End Sub

HtH

Pieter

I have a form where I ask 3 questions, who the report is for, the start
date
and the stop date. I would like to write code to filter a report for
that
data. Can someone show me how?