Adding another Text Box for a Report

[Guest]

Hello and a blessed day to all!

I have another question regarding reports...Please bear with me...

I wanted to add another text box in my current unbound form for ranging of
dates...

Here is what i have so far:

I have already an unbound form for ranging of dates, it includes two text
box StartDate and EndDate and when the user click Preview the report will
generate for that range of dates.

Here is the code for that:



Private Sub Command10_Click()

Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rpt_RangeofDate"
strField = "Date"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub


Now i will have to add another text box in that unbound form for Store
Number which the user will manually type and define which Store Number only
for that Range of Dates.

So if the user enter in the form: StartDate: 1/8/07 EndDate: 15/8/07 and the
user enter 49 in the Store Number text box and click Preview the report will
generate for the Store Number only inside the dates 1/8/07 - 15/8/07..

Now if the user leave the Store Number empty the report will generate and
will not filter any Store Number so basically it will generate all the store
number between 1/8/07 - 15/8/07

 

[John Spencer]

Private Sub Command10_Click()

Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rpt_RangeofDate"
strField = "Date"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

'============ Added lines of code ======================
' If store number is not a text field, but is a number field you will need
to
' remove the Chr(34) and extra & from the strWhere statement.
If Not IsNull(Me.txtStoreNumber) then
If Len(StrWhere) > 0 Then
strWhere = strWhere & " AND "
End IF
StrWhere = StrWhere & _
" StoreNumberFieldName = " & Chr(34) & Me.txtStoreNumber &
Chr(34)
End If
'============ Added lines of code ======================

DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..