parameter date question

[Steve Goodrich]

I am trying to create a parameter query on a date field.

The date is a time stamp - The property has been set to =now() on the table
the query is bound to.

I am trying to list a date range using the criteria below



Between [Please Enter Start Date] And [Please Enter End Date]



This lists the first date and the next to last date but will not show the
last date

e.g. typing 1 Dec 08 to 31 Dec 08 will show 1 Dec 08 to 30 Dec 08 (the last
day is missing)



The above criteria works ok on date fields that have not had their property
set to =now()



I have been told that Access treats every date as a number and if I enter
the time as well as the date it will work

Typing:1 Dec 08 to Dec 31 08 23:59:59 does in fact work.



My question:

Is there a way that I can add something to the criteria in my query so the
user just has to enter both dates and not worry about the time



Something like



Between [Please Enter Start Date] And [Please Enter End Date]+23:59:59



Thanks for any advice



Steve Goodrich

 

[Rick Brandt]

I am trying to list a date range using the criteria below

Between [Please Enter Start Date] And [Please Enter End Date]

This lists the first date and the next to last date but will not show
the last date

Typing:1 Dec 08 to Dec 31 08 23:59:59 does in fact work.

Is there a way that I can add something to the criteria in my query so
the user just has to enter both dates and not worry about the time
Something like

Between [Please Enter Start Date] And [Please Enter End Date]+23:59:59

You basically have it. What I do is not use BETWEEN, but instead use >=
on the start date and < on the last date, but I add a day to the value
the user enters.

WHERE FieldName >= [Please Enter Start Date]
AND FieldName < DateAdd("d", 1, [Please Enter End Date])

 

[Steve Goodrich]

Thanks Rick, Worked perfectly

Regards

Steve

I am trying to list a date range using the criteria below

Between [Please Enter Start Date] And [Please Enter End Date]

This lists the first date and the next to last date but will not show
the last date

Typing:1 Dec 08 to Dec 31 08 23:59:59 does in fact work.

Is there a way that I can add something to the criteria in my query so
the user just has to enter both dates and not worry about the time
Something like

Between [Please Enter Start Date] And [Please Enter End Date]+23:59:59

You basically have it. What I do is not use BETWEEN, but instead use >=
on the start date and < on the last date, but I add a day to the value
the user enters.

WHERE FieldName >= [Please Enter Start Date]
AND FieldName < DateAdd("d", 1, [Please Enter End Date])

 

[Tom Lake]

I am trying to create a parameter query on a date field.

The date is a time stamp - The property has been set to =now() on the
table the query is bound to.

I am trying to list a date range using the criteria below



Between [Please Enter Start Date] And [Please Enter End Date]



This lists the first date and the next to last date but will not show the
last date

If you don't need the time for any reason, don't use Now(), use Date()
Then your BETWEEN will work OK.

Tom Lake