"osti" <gottholmd@osti.gov> wrote:
>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.
>
> --- snip---
>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 message. The
>report dialog doesn't nothing at this point due. 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.
osti,

SQL requires dates in a specific format: date in US format (that is
mm/dd/yyyy) enclosed in # signs. So you must format the dates
accordingly. You could do this either with the Format function or use
the BuldCriteria function which also takes care of the # signs.

So you can try this:

Dim strWhereCategory As String

If Not IsNull(Forms!sfrmRptDialogTerms1!txtStartDate) Then
strWhereCategory = BuildCriteria("LOGIN_DATE", dbDate, " > " &
Forms!sfrmRptDialogTerms1!txtStartDate)
End If
MsgBox " sqlwhere clause=" & strWhereCategory

If Not IsNull(Forms!sfrmRptDialogTerms1!txtEndDate) Then
' If Not IsNull(strWhereCategory) Then
' I commented out the above line because it is not correct.
' A string variable can never be Null, it can only
' be a 'null string' which is "".
' So you have to check like this:
If strWhereCategory <> "" Then
strWhereCategory = strWhereCategory & " AND "
End If
strWhereCategory = strWhereCategory _
& BuildCriteria("LOGIN_DATE", dbDate, " < " &
Forms!sfrmRptDialogTerms1!txtEndDate)
End If
MsgBox " sqlwhere clause=" & strWhereCategory

As to why the MsgBox doesn't show up, there might be other causes. If
the routine runs, it should be displayed regardless to the form
control contents, so you might check in the caller Subs if the routine
actually gets started.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
[url]http://www.maxim-software-service.de[/url]