Limit Report on two date fields

A

ATSBC03

I have a report being filtered by multiple combo boxes (cboSite, cboDept,
cboType, etc.) and 2 date fields (txtEndDate and txtStartDate). The combo
boxes and date fields are on an unbound form that allows the user to select
any or all of the criteria to sort the report.
The problem I am having is trying to code the date fields. In my underlying
table there are two possible dates ([Date of Occurence] and [Date Reported])
I need the filter to check and allow the records between either date range
because sometimes the occurrence happens months before it is reported. Below
is my attempt at coding the on click command using information gathered from
the discussion group and Allen Browne's website. I have been unable to
incorporate the dates. I have been getting a type mismatch error and i'm not
sure where it is.

The other question I have is how to get the information gathered in the
unbound form to print in the header of the report. I would like to use if
statements due to the ability to not choose. For Example, if there is a site
and no type then i would like the header to print the site name and then
print "All types" to inform the user that no type was selected.

Any help would be appreciated. Thanks.

Private Sub cmdOK_Click()

Dim strDocName As String
Dim strWhere As String
Dim strOccDate As String
Dim strRptDate As String

Const conDateFormat = "\#mm\/dd\/yyyy\#"

strWhere = "1=1"
stDocName = "OccurrenceReportSpecSiteDept"
strOccDate = "[Date of Occurence]"
strRptDate = "[Date Reported]"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End Date, no start Date
strWhere = strWhere & " AND ( strOccDate & " <= " &
Format(Me.txtEndDate, conDateFormat)" _
& " Or " & strRptDate & " <= " & Format(Me.txtEndDate,
conDateFormat) & " )"
End If
Else
If IsNull(Me.txtEndDate) Then ' Start Date, no end date
strWhere = strWhere & " AND ( strOccDate & " >= " &
Format(Me.txtEndDate, conDateFormat)" _
& " Or " & strRptDate & " >= " & Format(Me.txtEndDate,
conDateFormat) & " )"
Else 'Both start and End Date
strWhere = strWhere & " AND ( strOccDate & "" Between "" &
Format(Me.txtStartDate, conDateFormat)" _
& "" And "" & Format(Me.txtEndDate, conDateFormat) _
& "" Or "" & strRptDate & " Between " &
Format(Me.txtStartDate, conDateFormat) _
& "" And "" & Format(Me.txtEndDate, conDateFormat) & " )"
Debug.Print strWhere
End If
End If

If Not IsNull(Me.cboSite) Then
strWhere = strWhere & " AND [Site] = """ & _
Me.cboSite & """"
End If

If Not IsNull(Me.cboType) Then
strWhere = strWhere & " AND [Type] = """ & _
Me.cboType & """"
End If

If Not IsNull(Me.cboDept) Then
strWhere = strWhere & " AND ( [Dept/Specialty] = """ & _
Me.cboDept & """ OR [Dept/Specialty2] = """ & _
Me.cboDept & """ OR [Dept/Specialty3] = """ & _
Me.cboDept & """ ) "

End If

Debug.Print strWhere

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

DoCmd.Close acForm, "frmSiteDept"

End Sub
 
K

KARL DEWEY

Seems like I had the same question out of what I now believe to be a student.

Use criteria under each of the fileds.
Like this --
[Date of Occurence] [Date
Reported]
=[Forms]![YourFormName]![txtSartMeter] >=[Forms]![YourFormName]![txtEndMeter]
Click to expand...
Use the criteria on the same row.

--
KARL DEWEY
Build a little - Test a little


ATSBC03 said:
I have a report being filtered by multiple combo boxes (cboSite, cboDept,
cboType, etc.) and 2 date fields (txtEndDate and txtStartDate). The combo
boxes and date fields are on an unbound form that allows the user to select
any or all of the criteria to sort the report.
The problem I am having is trying to code the date fields. In my underlying
table there are two possible dates ([Date of Occurence] and [Date Reported])
I need the filter to check and allow the records between either date range
because sometimes the occurrence happens months before it is reported. Below
is my attempt at coding the on click command using information gathered from
the discussion group and Allen Browne's website. I have been unable to
incorporate the dates. I have been getting a type mismatch error and i'm not
sure where it is.

The other question I have is how to get the information gathered in the
unbound form to print in the header of the report. I would like to use if
statements due to the ability to not choose. For Example, if there is a site
and no type then i would like the header to print the site name and then
print "All types" to inform the user that no type was selected.

Any help would be appreciated. Thanks.

Private Sub cmdOK_Click()

Dim strDocName As String
Dim strWhere As String
Dim strOccDate As String
Dim strRptDate As String

Const conDateFormat = "\#mm\/dd\/yyyy\#"

strWhere = "1=1"
stDocName = "OccurrenceReportSpecSiteDept"
strOccDate = "[Date of Occurence]"
strRptDate = "[Date Reported]"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End Date, no start Date
strWhere = strWhere & " AND ( strOccDate & " <= " &
Format(Me.txtEndDate, conDateFormat)" _
& " Or " & strRptDate & " <= " & Format(Me.txtEndDate,
conDateFormat) & " )"
End If
Else
If IsNull(Me.txtEndDate) Then ' Start Date, no end date
strWhere = strWhere & " AND ( strOccDate & " >= " &
Format(Me.txtEndDate, conDateFormat)" _
& " Or " & strRptDate & " >= " & Format(Me.txtEndDate,
conDateFormat) & " )"
Else 'Both start and End Date
strWhere = strWhere & " AND ( strOccDate & "" Between "" &
Format(Me.txtStartDate, conDateFormat)" _
& "" And "" & Format(Me.txtEndDate, conDateFormat) _
& "" Or "" & strRptDate & " Between " &
Format(Me.txtStartDate, conDateFormat) _
& "" And "" & Format(Me.txtEndDate, conDateFormat) & " )"
Debug.Print strWhere
End If
End If

If Not IsNull(Me.cboSite) Then
strWhere = strWhere & " AND [Site] = """ & _
Me.cboSite & """"
End If

If Not IsNull(Me.cboType) Then
strWhere = strWhere & " AND [Type] = """ & _
Me.cboType & """"
End If

If Not IsNull(Me.cboDept) Then
strWhere = strWhere & " AND ( [Dept/Specialty] = """ & _
Me.cboDept & """ OR [Dept/Specialty2] = """ & _
Me.cboDept & """ OR [Dept/Specialty3] = """ & _
Me.cboDept & """ ) "

End If

Debug.Print strWhere

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

DoCmd.Close acForm, "frmSiteDept"

End Sub
Click to expand...
 
A

ATSBC03

Thank you for your reply. I looked for similar questions in the discussion
group and have not been able to find one, however, I am not a student. The
answer you gave is what i would use in a query. I am not using a query and
instead am coding into my unbound form the criteria for filtering the report.
I looked for similar questions to what I am having problems with but was
unable to find anything to help me.

KARL DEWEY said:
Seems like I had the same question out of what I now believe to be a student.

Use criteria under each of the fileds.
Like this --
[Date of Occurence] [Date
Reported]
=[Forms]![YourFormName]![txtSartMeter] >=[Forms]![YourFormName]![txtEndMeter]
Click to expand...
Use the criteria on the same row.

--
KARL DEWEY
Build a little - Test a little


ATSBC03 said:
I have a report being filtered by multiple combo boxes (cboSite, cboDept,
cboType, etc.) and 2 date fields (txtEndDate and txtStartDate). The combo
boxes and date fields are on an unbound form that allows the user to select
any or all of the criteria to sort the report.
The problem I am having is trying to code the date fields. In my underlying
table there are two possible dates ([Date of Occurence] and [Date Reported])
I need the filter to check and allow the records between either date range
because sometimes the occurrence happens months before it is reported. Below
is my attempt at coding the on click command using information gathered from
the discussion group and Allen Browne's website. I have been unable to
incorporate the dates. I have been getting a type mismatch error and i'm not
sure where it is.

The other question I have is how to get the information gathered in the
unbound form to print in the header of the report. I would like to use if
statements due to the ability to not choose. For Example, if there is a site
and no type then i would like the header to print the site name and then
print "All types" to inform the user that no type was selected.

Any help would be appreciated. Thanks.

Private Sub cmdOK_Click()

Dim strDocName As String
Dim strWhere As String
Dim strOccDate As String
Dim strRptDate As String

Const conDateFormat = "\#mm\/dd\/yyyy\#"

strWhere = "1=1"
stDocName = "OccurrenceReportSpecSiteDept"
strOccDate = "[Date of Occurence]"
strRptDate = "[Date Reported]"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End Date, no start Date
strWhere = strWhere & " AND ( strOccDate & " <= " &
Format(Me.txtEndDate, conDateFormat)" _
& " Or " & strRptDate & " <= " & Format(Me.txtEndDate,
conDateFormat) & " )"
End If
Else
If IsNull(Me.txtEndDate) Then ' Start Date, no end date
strWhere = strWhere & " AND ( strOccDate & " >= " &
Format(Me.txtEndDate, conDateFormat)" _
& " Or " & strRptDate & " >= " & Format(Me.txtEndDate,
conDateFormat) & " )"
Else 'Both start and End Date
strWhere = strWhere & " AND ( strOccDate & "" Between "" &
Format(Me.txtStartDate, conDateFormat)" _
& "" And "" & Format(Me.txtEndDate, conDateFormat) _
& "" Or "" & strRptDate & " Between " &
Format(Me.txtStartDate, conDateFormat) _
& "" And "" & Format(Me.txtEndDate, conDateFormat) & " )"
Debug.Print strWhere
End If
End If

If Not IsNull(Me.cboSite) Then
strWhere = strWhere & " AND [Site] = """ & _
Me.cboSite & """"
End If

If Not IsNull(Me.cboType) Then
strWhere = strWhere & " AND [Type] = """ & _
Me.cboType & """"
End If

If Not IsNull(Me.cboDept) Then
strWhere = strWhere & " AND ( [Dept/Specialty] = """ & _
Me.cboDept & """ OR [Dept/Specialty2] = """ & _
Me.cboDept & """ OR [Dept/Specialty3] = """ & _
Me.cboDept & """ ) "

End If

Debug.Print strWhere

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

DoCmd.Close acForm, "frmSiteDept"

End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problems Filtering Report with Form 0
Use combo to print report between two sets of dates 1
Combine Code 2
Adding another Text Box for a Report 1
Mixing 2 reports together 0
visual basic 1
Filter Report by Form 2
Run time error 2

Top