Please help w/form collecting parameters for Report

[TotallyConfused]

I am trying to create a form that will collect parameters for a Report. In
searching for help on this I came across Allen Brownes Access Tip, "Limiting
a Report to a Date Range". However, the code below works fine for one date.
My report has 4 date parameters that need to be filled by the form. How can
this be achieved using Mr. Browne's code? I have an Appt date, CareStart
Date and CareEndDate, HospStart Date and HospEndDate and ServiceStartDate and
ServiceEndDate.

Any help will be greatly appreciated. Thank you.


Private Sub OK_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 PPC Fax Cover Sheet"
strField = "text109"

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtstartdate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtstartdate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

 

[Albert D. Kallal]

strField = "text109"

You don't really have a date field named the above? (remeber, the above has
NO relationship to the name of a contorl on your form...we talking about
FIELD NAMES IN THE TABLE!!!


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


If IsNull(Me.txtStartDate) = False Then
strWhere = "InvoiceDate >= " & Format(Me.txtStartDate,
conDateFormat)"
End If

If IsNull(Me.txtEndDate) = False Then
If strWhere <> "" Then
strWhere = strWhere & " and "
End If

strWhere = "InvoiceDate <= " & Format(Me.txtEndDate, conDateFormat)
End If

Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere

The above will do a date range for ONE field, in this example InvoiceDate

You also not mentioned what text box controls on your form are to filter
what fields in the table/report.

For example, you have on your form, obviously two text boxes for a date
range...but fail to mention what date field that me.txtStartDate is to
filter?
(ie: what field - Appt date, CareStart
Date and CareEndDate, HospStart Date and HospEndDate and ServiceStartDate
and
ServiceEndDate.).

Is the simple entering of ONE date into me.txtStartDate to filter on ALL of
the above fields? You made no connection between what controls on your form
are to be filtered by what controls. The above code snip can be repeated
over and over for each set of text boxes and fields in the table (you have
to add a set of start/end text boxes on that form and use code as above to
build the field criteria.

I suggest you get ONE text box filter working...and then add each
additional piece of code for each additional text box you place on your
filter form....

Also note in the above example how I've not used in if then...else structure
to distinguish between two dates, and selecting a "between" command fror the
sql.

Reallly, what happens is:


if startdate then

strWhere - "InvoiceDate >= #some date#

at this point in time are where is correctly formed. if the user does decide
to enter an ending date, then we simply append that

eg:

strWhere = strWhere & "InvoiceDate <= #some date#"

at this point I'm we now have

strWhere = "InvvoiceDate >= #some date# and InvoiceDate <= "#some date#"

we *could* write the above as:

strWhere = "InvvoiceDate between #some date# and "#some date#"

However both have the same logical meaning, and my approach takes a whole
heck of a lot less code as we don't have to start concerning about usng
"between" or "and" in the syntax. just dump the use of between altogether,
and you can see that the code will flow and write and work a lot easier.

Once you get the code snippet working for one set of text boxes on your
form, then you can relatively with ease cut and paste over and over the same
piece of code for the additional sets of text boxes and gate fields that you
have. Also, you might want a reintroduce your idea of having a variable for
the date field, since the date code is likely going to look much the same
for the next four sets of fields that you use.

eg:

use:
strField = "InvoiceDate"

If IsNull(Me.txtStartDate) = False Then
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)"
End If

So, get this working with ONE date field. also keep in mind that using
spaces in field names will keep you frustrated in the salt mines dealing
with spaces in your SQL. that if you're using spaces in your field names
then you should likely put square brackets around them

eg:

strField = "[Invoice Date]"