Data not consistent with Parameter Query (1 Viewer)

[Rats]

I use the following code in an application I use at work. It is used to query my data for records that fall between the start and finish dates provided in the parameter form "Office Reports". We have just noticed that when doing a monthly report for June that work done on the last day of the month ie 30/6/08 is not appearing. If I select the 1/7/08 as the finish date then the last days work appears. I have gone over the code and it seems to be correct. Can anyone give me a pointer in how to get all the dates that fall on and between the dates given in the parameters please?

SELECT [Client action].advisors, [Client action].Client1Name1, [Client action].Client1Name2, [Client action].MasterPlanCompleted, [Client action].company
FROM [Client action]
WHERE ((([Client action].MasterPlanCompleted) Between [forms]![Office Reports]![StartDate] And [forms]![Office Reports]![FinishDate]))
ORDER BY [Client action].advisors;

 

[raskew]

Hi -

Does your [FinishDate] include time? If so, then that field will be greater than just the date, e.g.

' date only as stored by Access
? cdbl(#6/30/08#)
39629

' date and time as stored by Access
? cdbl(#6/30/08 09:00:00 AM#)
39629.375

you may want to change your approach to something like:

>= [StartDate] and <([FinishDate] + 1)
-or-
>= [StartDate] and <(DateValue([FinishDate]) + 1)

HTH - Bob

 

[Rats]

Hi Bob

Thanks for that. I had already added the +1 and achieved the right result so I can leave that as it is then. I was just puzzled that it wasn't picking it up as the code seemed to be OK but you have explained that and it makes sense.