Date parameter

[pon]

I have Begin and end date as parameters in my query.
The data in the table is crazy Some dates are 03/28/2007 10:30:27 AM and
some are 03/28/2007.
I tried the following:
1.I formated it with the help of expression but the field became text.

2.I created the criteria Between [Begin Date] And DateAdd("d",1,[End Date]).
For the parameter 03/27/2007 and 03/28/2007
the results include 03/27/2007 10:30:27 AM and 03/28/2007 and 03/29/2007

3.I created the criteria as >= [Begin Date] and < [End Date]
For the parameter 03/27/2007 and 03/28/2007
the results include 03/27/2007 10:30:27 AM and 03/28/2007
and they don't include the data which is like 03/28/2007 10:23:25 PM

How can I make this work?
Please help.

 

[Jerry Whittle]

Question #1: Is the field in question a Date data type? Look at it in table
design mode. There's a very good chance that it's a text field with things
that look like dates.

Question #2: Do you have the parameters set to Date data types? How to find
out: Open the query in design view. Next go to View, SQL View. If the first
part of the statement doesn't say something like below, your parameter
probably are being evaluated as text especially if the field is text.
PARAMETERS [BEGIN DATE] DateTime, [END DATE] DateTime;

 

[raskew via AccessMonster.com]

Jerry -

Take a look at this thread. I provided a potential fix, assuming that dates
were in date-type data type, with no times involved. Rick pointed out the
folly of that if there could be times involved. Hopefully, this will give
you the direction you need to take.

http://www.accessmonster.com/Uwe/Fo...th-then-query-for-that-month#7bb378e865383uwe


HTH - Bob

Question #1: Is the field in question a Date data type? Look at it in table
design mode. There's a very good chance that it's a text field with things
that look like dates.

Question #2: Do you have the parameters set to Date data types? How to find
out: Open the query in design view. Next go to View, SQL View. If the first
part of the statement doesn't say something like below, your parameter
probably are being evaluated as text especially if the field is text.
PARAMETERS [BEGIN DATE] DateTime, [END DATE] DateTime;

I have Begin and end date as parameters in my query.
The data in the table is crazy Some dates are 03/28/2007 10:30:27 AM and

[quoted text clipped - 13 lines]

How can I make this work?
Please help.

 

[John Spencer]

Use your option 3 with the DateAdd

= [Begin Date] and < DateAdd("d",1,[End Date])

For the parameter 03/27/2007 and 03/28/2007
the results include 03/27/2007 10:30:27 AM and 03/28/2007
and they don't include the data which is like 03/28/2007 10:23:25 PM

That will get everything from midnight 3/27 up to but not including
midnight on 3/29

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Jerry -

Take a look at this thread. I provided a potential fix, assuming that dates
were in date-type data type, with no times involved. Rick pointed out the
folly of that if there could be times involved. Hopefully, this will give
you the direction you need to take.

http://www.accessmonster.com/Uwe/Fo...th-then-query-for-that-month#7bb378e865383uwe


HTH - Bob

Question #1: Is the field in question a Date data type? Look at it in table
design mode. There's a very good chance that it's a text field with things
that look like dates.

Question #2: Do you have the parameters set to Date data types? How to find
out: Open the query in design view. Next go to View, SQL View. If the first
part of the statement doesn't say something like below, your parameter
probably are being evaluated as text especially if the field is text.
PARAMETERS [BEGIN DATE] DateTime, [END DATE] DateTime;

I have Begin and end date as parameters in my query.
The data in the table is crazy Some dates are 03/28/2007 10:30:27 AM and

[quoted text clipped - 13 lines]

How can I make this work?
Please help.