Trouble with Code for date filter on report...

[Lynndyhop]

Hi there!

Wondering if I could get some help de-bugging....I have a form
(DateInputForm) that gets a start and end date and then on a cmd button 'OK'
there is the following code to filter dates for a report, based upon field
'ContactDate' in the report. It's designed to filter on dates in the
following way:

-Start date only - filter from start date on - PROBLEM - Filters everything
out.
-End date only - filter anything before end date - PROBLEM - Filters
everything out.
-No dates - Don't filter - PROBLEM - filters everything
-Both dates - Filter between - WORKS!

Here is the code - Any ideas? I think it may not recognize the values as
null, but not sure what to do or how to test.... (Many thanks!!)

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 = "\#dd\/mm\/yyyy\#"

strReport = "ContactsSummaryFilterReport"
strField = "ContactDate"

If IsNull(Me.StartDateInput) Then
If Not IsNull(Me.EndDateInput) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.EndDateInput,
conDateFormat)
Else
DoCmd.OpenReport strReport, acViewPreview 'If no start or end
date.
End If
Else
If IsNull(Me.EndDateInput) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.StartDateInput,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.StartDateInput,
conDateFormat) _
& " And " & Format(Me.EndDateInput, conDateFormat)
End If
End If
Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere

 

[Allen Browne]

Issue #1: This line is incorrect:
Const conDateFormat = "\#dd\/mm\/yyyy\#"

Regardless of your local date setting, you must use the format JET expects,
i.e.:
Const conDateFormat = "\#mm\/dd\/yyyy\#"

Issue #2: Remove all the DoCmd.OpenReport lines except the last one.

 

[Lynndyhop]

Hi Allen,

Thanks for your help - I've changed the date format (in UK, which is why I
thought it would need changing, so thankyou for letting me know to change it
back)

I took out the Else
DoCmd.... - this was to address the condition of no
dates entered

it is still behaving the same way - no change. Any other thoughts? I had
actually followed a link from your site to put this together from previous
post, so very happy to have your help too!

 

[Dominic Vella]

Mind you, you could use a medium date format, eg dd-mmm-yyyy, and that will
work US or UK.

Dom

 

[Allen Browne]

So now you have something like the code below.

That should work. If StartDateInput and EndDateInput are unbound, make sure
that:
a) you have set their Format property to Short Date or similar, and
b) you do not assign a non-date value to them. For example don't assign a
zero-length string, and don't set their DefaultValue to "".

We assume that ContactDate is a Date/Time field (not a Text field.) There
could be an additional issue if it contains a time as well as a date.

If that still doesn't work, post what you get in the Immediate Window
(Ctrl+G) after you run the report.

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 = "\#dd\/mm\/yyyy\#"

strReport = "ContactsSummaryFilterReport"
strField = "ContactDate"

If IsNull(Me.StartDateInput) Then
If Not IsNull(Me.EndDateInput) Then 'End date, but no start.
strWhere = strField & " <= " & _
Format(Me.EndDateInput, conDateFormat)
End If
Else
If IsNull(Me.EndDateInput) Then 'Start date, but no End.
strWhere = strField & " >= " & _
Format(Me.StartDateInput, conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & _
Format(Me.StartDateInput, conDateFormat) & _
" And " & Format(Me.EndDateInput, conDateFormat)
End If
End If
Debug.Print strWhere
DoCmd.OpenReport strReport, acViewPreview, , strWhere

 

[Lynndyhop]

Would the user have to input it like that? They are inputting in short date
format, which in the UK is dd mm yyyy. Tried the suggested format and it
didn't change anything. thanks for the ideas though!!

Lynndyhop

 

[Lynndyhop]

Hi Allen,

1) Your assumption is correct - ContactDate is a Date/Time field - Short
Date. There was an Input Mask format. So I switched it to 'No characters
stored with the number', but then I'm getting an error when I run the script,
and I'd still get one when I put it back. So I've completely re-entered the
short date format and it's showing an Input Mask of: 00/00/0000;0;_ Though
to me, this means that if nothing is keyed by the end user, it's not null. Is
this right?
2) I have ensured that both StartDateInput and EndDateInput are formatted
with ShortDate format.
3) How do I check if a zero length string has been assigned? There is no
default set in the properties box. I'm thinking this may have to do with the
above question.

Leaving the input mask in, here's what I get with cntrl G:

When entering both dates:
ContactDate Between #01/01/1800# And #15/11/2009#
When entering no dates:
I get nothing - just a blank line
When entering Start Date only:
ContactDate >= #01/01/1779#
And when entering End Date only:
ContactDate <= #31/12/2009#

Should the '#'s be there?

Thanks again!

Lynndyhop

 

[Allen Browne]

Remove the input mask until you get it working. (It's generally not helpful
IME, but could mask what's really there.)

You have not yet corrected the line:
Const conDateFormat = "\#mm\/dd\/yyyy\#"
Your example of:
ContactDate Between #01/01/1800# And #15/11/2009#
won't work properly because there is no 15th month.

If you are having difficulty, I have just updated the code at:
http://allenbrowne.com/casu-08.html
so it explicitly deals with the kinds of issues that could cause grief. It's
a little longer, but just copy'n'paste, and change only the 3 lines at the
top and the names of the unbound controls.

 

[Lynndyhop]

Hi Allen,

Corrected the date to mm\/dd\/yyyy - though the system continues to change it.

The example ' ContactDate Between #01/01/1800# And #15/11/2009#' was from
the date being uk - formatted. So using the new code here's another go:

Start Date only: ([ContactDate] >= #01/01/1901#)
End Date only: ([ContactDate] < #01/01/2010#) (this was from entering
12/31/2009)
Both Dates: ([ContactDate] >= #01/01/1801#) AND ([ContactDate] < #01/01/2009#)
No Dates: Nothing.

Sigh....thanks for your continued help and patience!!

Lynndyhop

 

[Allen Browne]

There must be something else going on.

Perhaps you can create a query the same as the one that feeds the report,
and test the expression in the Criteria there.