'Between...and' problems

[wiltshire]

Hi There,

I'm gettng really frustrated with a query that should be quite simple.

I have a query that pulls out data from a table based on user inputs for 'start date' and 'end date'. This is done using the "between [Start Date[ and [End Date]" function in the query. This sounds simple enough but it's returning some strange results.

For example, I'm entering 17/12/04 for start date and 04/01/05 for end date. This is returning a recordset but one record which I know was done on the 04/01/05 is not showing. If I change the data entered to 17/12/04 and 05/01/05, the record shows.

I've checked the help files in Access and it says that the 'between...and' function is inclusive of the dates entered, so should include the 04/01/05 I entered

Has anyone else encountered a problem with this function and know how I can get it to return the data accurately. I'm getting quite a lot of hassle for it at the moment because a high level report in my company is based on it !!

Thanks

 

[FoFa]

Usually only when TIME is envloved with the date. For example (USA date formats), 01/04/2005 defaults to 01/04/2005 00:00:00, so 01/04/2005 11:00:00 would fall outside that range.
You can check this by hard coding the end date only and those dates show up (like you did in the example). One other thing you can try is taking your end date, and adding one day to it, usually not an issue if it is time related along with the date.

 

[wiltshire]

Thanks FoFa,

I'm with a colleague at the moment and we just realised it was the time stamp causing the problem but didn;t know why - your reply explains why

Thanks for you help

Steve

 

[wiltshire]

ok - next question - FoFa kindly explained that it's the time stamp I have that is casuing the problem with my query.

How do I extract just the date part of the date/time field and still leave the result as a date?

Thanks

 

[FoFa]

If the dates are indexed on your table, then NOT extracting the date part is better. If you format an index, the system does not use it as an index anymore (not 100% about access, but other engines do that). Like I said before, you are usually better off adding 1 day to your end date. Or you can add one day to your end date and subtract one second to change it to 01/05/2005 23:59:59 as an example. Just use the DATEADD function, so if it is 01/05/2005 it would be.
DATEADD("s",-1,DATEADD("d",1,[MyEndDate])) (use help to verify the syntax)
and just use this in your Between where you are currently using your [myEndDate]

 

[wiltshire]

Thanks for the info FoFa.

Adding one day would not normally cause a problem, but the data I'm working with is the quantitiy of failures within a production environment. The last thing I want to do is panic the management team by double counting failures when two consecutive weeks are reported. Isn't that what this would do if the failure was on the last day of the range, which would then become the first day of the next report if I add 1???

 

[wiltshire]

Sorry FoFa, the big penny just dropped. I suddenly realised what you meant by adding a day and taking a second off. I'll try that.

Is this the only way, it seems a bit of a complicated way of looking at the date value of a date/time field??

Thanks

 

[FoFa]

True, you can strip the date from the database using a shortdate format and then putting it inside #'s. Or you can create a datetime string from your enddate and place that inside #'s, there many approaches to this. Just F.Y.I. I usually use >= StartDate AND < Enddate +1 day. In this way the enddate ends up as 01/06/2005 00:00:00, and anything less than that would be before 01/06/2005. Between is a basic >= Date AND <= Date, so you see it is the "=" part of the enddate that causes you problems.

 

[wiltshire]

your right - that sounds easier.

Thanks for all of your help

Steve

 

[wiltshire]

Just tried your suggestion and it works perfectly.

Cheers !!!

 

[EMP]

Is this the only way, it seems a bit of a complicated way of looking at the date value of a date/time field??

Access has a DateValue() function for extracting the date value from a date/time field.

You can use the function in a query like this:-

SELECT [FieldA], [FieldB], ......
FROM [TableName]
WHERE DateValue([DateTimeField]) between [Start Date] and [End Date]

-