Basic Filtering For Data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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?

Pieter Wijnen said:
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 said:
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?
Click to expand...
Click to expand...
 
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?
Click to expand...

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]))
Click to expand...

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

John W. Vinson [MVP]
 
They represent the actual controls on your form, which may (should) differ
from yor table's field names

HtH

Pieter

williamr said:
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?

Pieter Wijnen said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top