Date limited report

[LaurieTuff via AccessMonster.com]

XP and Access 2002

Public SelectDate as Date

If SelectDate is 14/10/2009 the following works but if SelectDate is
4/10/2009 (any single digit day of month) the report fails

Me.Filter = "[ResActivity Query].[ResidentID] = " & SelectRecord

Me.Filter = "((" & Me.Filter & ") and ([ResActivity Query].Date = #" &
SelectDate & "#))"

What am I doing wrong??

 

[Jeff Boyce]

"the report fails" is not very informative...

Does your PC melt down? Does it lock up? ... or does it present an error
message? If so, it might help us diagnose if we knew what that message
is...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

Try an unambiguous date format yyyy-mm-dd

Me.Filter = "((" & Me.Filter & ") and ([ResActivity Query].Date = #" &
Format(SelectDate,"yyyy-mm-dd") & "#))"

The problem is probably that Access is returning the date April 10 2009 (US
format instead of October 4, 2009 as you expect.

With 14/10/2009 Access knows there is no 14th month, so it rearranges the
input to make 14 the day and 10 the month.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[LaurieTuff via AccessMonster.com]

Sorry - I was a little vague about the failure mode! However John, that was
the problem and it is now resolved. Thanks for your input.

Regards, Laurie

Try an unambiguous date format yyyy-mm-dd

Me.Filter = "((" & Me.Filter & ") and ([ResActivity Query].Date = #" &
Format(SelectDate,"yyyy-mm-dd") & "#))"

The problem is probably that Access is returning the date April 10 2009 (US
format instead of October 4, 2009 as you expect.

With 14/10/2009 Access knows there is no 14th month, so it rearranges the
input to make 14 the day and 10 the month.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

XP and Access 2002

[quoted text clipped - 9 lines]

What am I doing wrong??