date parameter in query not working quite right

  • Thread starter Thread starter Bongard
  • Start date Start date
B

Bongard

Hi I have a Query [qry_master] which contains a field [notedate] I want
the user to be able to specify an as of date and I want that as of date
to include the date that they specify. Currently in my fields criteria
line I have

<=[Enter an As Of Date]

But when the query runs it never included the entered dates
information. Does anyone have any ideas why this might be?

Thanks!
Brian
 
Bongard said:
Hi I have a Query [qry_master] which contains a field [notedate] I
want the user to be able to specify an as of date and I want that as
of date to include the date that they specify. Currently in my fields
criteria line I have

<=[Enter an As Of Date]

But when the query runs it never included the entered dates
information. Does anyone have any ideas why this might be?

Thanks!
Brian
Click to expand...

If your data includes times other than midnight then a "date only" search
will only find records where the time is exactly midnight on that day. Try
this instead...

< DateAdd("d", 1, [Enter an As Of Date])
 
Rick said:
If your data includes times other than midnight then a "date only" search
will only find records where the time is exactly midnight on that day. Try
this instead...

< DateAdd("d", 1, [Enter an As Of Date])
Click to expand...

Your suggestion would include DATETIME values *beyond* the day of the
parameter value e.g.

CREATE TABLE Test (
AsOfDate DATETIME NOT NULL
)
;
INSERT INTO Test VALUES (#2001-01-01 00:00:00#)
;
INSERT INTO Test VALUES (#2001-01-01 01:00:00#)
;
INSERT INTO Test VALUES (#2001-01-02 00:00:00#)
;
INSERT INTO Test VALUES (#2001-01-02 01:00:00#)
;
CREATE PROCEDURE ProcTest (
[Enter an As Of Date] DATETIME = DATE()
) AS
SELECT AsOfDate
FROM Test
WHERE AsOfDate < DATEADD('d', 1, [Enter an As Of Date])
;
EXECUTE ProcTest #2001-01-01 12:00:00#
;

The above returns all four rows, including those that fall on the day
after the day of the parameter value.

I think the easiest fix would be to wrap the parameter value in the
DATEVALUE function:

DROP PROCEDURE ProcTest
;
CREATE PROCEDURE ProcTest ([Enter an As Of Date] DATETIME = DATE()) AS
SELECT AsOfDate
FROM Test
WHERE AsOfDate < DateAdd('d', 1, DATEVALUE([Enter an As Of Date]))
;
EXECUTE ProcTest #2001-01-01 12:00:00#
;

Now only the dates that fall on the day of the param value are
returned.

Jamie.

--
 
Back
Top