OpenReport with date fields parameters

[Dave]

I am trying to use a report dialog form that has three
fields that will be used to filter the SQL in the report.
I've included the print report code that gets called form
the report dialog box below:

PROBLEM:
One is a text field so that the user can enter a wildcard
term. This works fine. I can enter a value and it calls
the report and executes correctly with the filter.

The other two fields are text boxes that the user can
enter dates into and then I add a > then the start date
and/or a < the end date. They can enter one or the other
or both (why I'm not using between). Oh, and I also have a
calendar control that lets them pick the dates from. It
returns the date into the date text boxes.

It works for the two dates fields if I have no format mask
and I do not using the calendar control. In this case I
get the msgbox that prints out the syntax of the
strWhereCategory. Everything works fine, it generates the
report with the filter for the dates.

The problem is when I either a: add a format mask (i.e.
dd/mmm/yyy) to the date text boxes or I use the calendar
control and return the values into the date text boxes.
In both cases, I do not even get the MsgBox debug
message. The report dialog doesn't nothing at this
point. Although it works if I manually enter the date
values with no format mask on the text fields, I really
want to use a format mask and especially the calendar
control to make it easier for the user.

Anyone have any ideas as to why the format mask and the
calendar control affect the field so that it does not work?

PRINT REPORT CODE
=================
Sub PrintReports(PrintMode As Integer)
On Error GoTo Err_Preview_Click
' This procedure used in Preview_Click and Print_Click
Sub procedures.
' Preview or print report selected in the
ReportToPrint option group.
' Then close the Print Sales Reports Dialog form.

Dim strWhereCategory As String

If (Not IsNull(Forms!sfrmRptDialogTerms1!
txtStartDate)) Then
strWhereCategory = " LOGIN_DATE > #" + Forms!
sfrmRptDialogTerms1!txtStartDate + "#"
End If
MsgBox " sqlwhere clause=" + strWhereCategory

If (Not IsNull(Forms!sfrmRptDialogTerms1!txtEndDate))
Then
If Not IsNull(strWhereCategory) Then
strWhereCategory = strWhereCategory + " AND "
End If
strWhereCategory = strWhereCategory + "LOGIN_DATE <
#" + Forms!sfrmRptDialogTerms1!txtEndDate + "#"
End If
MsgBox " sqlwhere clause=" + strWhereCategory

Select Case Me!ReportToPrint.Value
Case 1
DoCmd.OpenReport "RptDistinctTermsList",
PrintMode, , strWhereCategory
Case 2
If (Not IsNull(Forms!sfrmRptDialogTerms1!
fWildCardCriteria)) Then
If Not IsNull(strWhereCategory) Then
strWhereCategory = strWhereCategory + "
AND "
End If
strWhereCategory = strWhereCategory + "
QUERY_TERMORPHRASE like " + "'*" + Forms!
[sfrmRptDialogTerms1]!fWildCardCriteria + "*'"
' MsgBox "sqlwhere clause=" +
strWhereCategory
End If
DoCmd.OpenReport "RptDistinctTermsList",
PrintMode, , strWhereCategory
End Select
DoCmd.Close acForm, "sfrmRptDialog1"

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
Resume Exit_Preview_Click

End Sub

 

[Larry Linson]

Anyone have any ideas as to
why the format mask and the
calendar control affect the field
so that it does not work?

No, and without trying to review your code to spot an anomaly (that's not
often a productive, efficient use of time), I can make you some suggestions.
After you create the WHERE clause or the WhereCondition for DoCmd.Open
Report, look at what you have created -- chances are that it isn't what you
expected to create.

You can do it by displaying the string in a MsgBox or Debug.Print or put a
stop in the code and look at the value you stored in the variable via the
Immediate/Debug Window.

Taking a second look at your code to spot obvious errors is a good approach,
but you often must follow it up with looking deeper.

Larry Linson
Microsoft Access MVP