Date parameter query

[Eugene]

Hi all

Thanks for my previous reply about date parameters, but I still have
the following problem.

I have a time/date field in a table. The data in this field is shown
as: 30/04/02 02:56:25 PM. When I want to do a query I normally use a
parameter for the date field eg. >=[Start date] and < [end date}.
When the query runs, I get info based on the dates that I enter (I
only enter dates eg. 12/08/2003).

On this newsgroup I got the following formula that I put in the
criteria field for the date: =IIF(Weekday(Date())=2,
DateAdd("d",-3,Date()),DateAdd("d",-1,Date())). This formula produces
no result for the query.

Anyone know why this could be?

Thanks
Eugene

 

[Jeff Boyce]

Eugene

If you leave the formula out of the criterion field, but put in a "hard"
date, does it work?

The fact that your data is DISPLAYED as "30/04/02 ...." doesn't mean that's
what's stored in the table. Just guessing, but the date format may be
encountering a conflict between "US" date formatting (mm/dd/yy) and
"European" ("dd/mm/yy").

If you are comparing a date/time ("30/04/02 02:56:25 PM") to a date-only
value ("30/04/02"), these two will never match, right?! One approach would
be to return the date value portion of your date field, so you could compare
like values.

Note: the other date you mentioned ("12/08/2003") could be a valid date
under either US/European format, but the earlier one could only be valid
under European format.

 

[John Spencer (MVP)]

IF I understand you correctly, you are searching for an exact match of the
datetime field that is equal to midnight on the calculated date. If you are
trying to get all all activity for the date you have at least a couple of choices.

One - Wrap your date time field with DateValue in the query
Field: JustTheDate: DateValue(YourDateTimeField)
Criteria: =IIF(Weekday(Date())=2,
DateAdd("d",-3,Date()),
DateAdd("d",-1,Date()))

Two - calculate the date range for the criteria as

= IIF(Weekday(Date())=2, DateAdd("d",-3,Date()),DateAdd("d",-1,Date()))

AND < IIF(Weekday(Date())=2, DateAdd("d",-2,Date()),DateAdd("d",0,Date()))

Hi all

Thanks for my previous reply about date parameters, but I still have
the following problem.

I have a time/date field in a table. The data in this field is shown
as: 30/04/02 02:56:25 PM. When I want to do a query I normally use a
parameter for the date field eg. >=[Start date] and < [end date}.
When the query runs, I get info based on the dates that I enter (I
only enter dates eg. 12/08/2003).

On this newsgroup I got the following formula that I put in the
criteria field for the date: =IIF(Weekday(Date())=2,
DateAdd("d",-3,Date()),DateAdd("d",-1,Date())). This formula produces
no result for the query.

Anyone know why this could be?

Thanks
Eugene