Between 2 dates query misses end date AGAIN! 1

[Larft]

I've been through this before and was able to use the DateValue solution but now it's not working. I am trying to set up a simple (yeah right, no such thing in Access for this one) parameter query that returns records between two dates:

"Between [Beginning Date] And [Ending Date]" on a ShortDate date/time field.

Problem is the time component is included in the record when it is automatically generated by my form's AfterUpdate event and everything past the exact stroke of midnight on the end date is omitted since it's not "Between".

I've searched and tried several solutions, no luck, the DateValue value function has worked before but it's not doing it now. I can get it to strip the time from the date by creating a "DateOnly' expression in the query design and then add the parameter portion to the criteria section but it still misses the last date, I get the same result as I did without the added functions.

Anybody out there have any suggestions? This is a good one for a FAQ/Help topic to clearly describe the solution.

Thanks in advance!

 

[Golom]

Short Date" is a display format thing. It is not related to what is stored in the DateTime field.

Try something like

"Between [Beginning Date] And [Ending Date] + 1"

What you really want is dates that are before midnight of the day following your [Ending Date].

 

[Larft]

I agree and that has also worked before. I tried it already several ways and I get a "too complex" error, fewer records or no change at all depending on how I try using it.

If it helps, here is the sql code:

SELECT CustomerMaster.CNAME, Verification.TechCode, Verification.[LWR#], Verification.DateCompleted, DateValue([DateCompleted]) AS DateOnly
FROM CustomerMaster RIGHT JOIN Verification ON CustomerMaster.CUSNO = Verification.[Cust#]
GROUP BY CustomerMaster.CNAME, Verification.TechCode, Verification.[LWR#], Verification.DateCompleted
HAVING (((Verification.DateCompleted) Is Not Null) AND ((DateValue([DateCompleted])) Between [begin] And [end]))
ORDER BY CustomerMaster.CNAME, Verification.[LWR#];

Thanks!

 

[PHV]

WHERE Int([date field]) Between Int([Beginning Date]) And Int([Ending Date])

or

WHERE Format([date field],'yyyymmdd') Between Format([Beginning Date],'yyyymmdd') And Format([Ending Date],'yyyymmdd')


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

You need to be explicit about data types for parameter values

PARAMETERS [Begin] DateTime, [End] DateTime;

SELECT C.CNAME, V.TechCode, V.[LWR#], V.DateCompleted,
       DateValue([DateCompleted]) AS DateOnly

FROM CustomerMaster C RIGHT JOIN Verification V 
     ON C.CUSNO = V.[Cust#]

WHERE V.DateCompleted Is Not Null 
      AND DateValue([DateCompleted]) Between [Begin] And [End] + 1

GROUP BY C.CNAME, V.TechCode, V.[LWR#], V.DateCompleted

ORDER BY C.CNAME, V.[LWR#];

The constraints should be in a WHERE clause because they are field constraints ... not aggregate function constraints.

 

[Larft]

Thanks to PHV and Golom!

PHV, I couldn't get you suggestion to work for me without a syntax error, probably something simple but I'm still learning.

Golom, Your code suggestion gets the star, I was able to add it to my query with a little editing and it works just as it should. Thanks for the pointer on the constraints criteria, I need to dig into this area further or it will rear it's ugly head again. It seems like this is one area where things could have been done better by MS, but aren't there a lot of those?

Thanks again!

 

[PHV]

without a syntax error
My post was a portion of SQL code, not something to past in the criteria cell ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dandot]

I'm having hte same problem. I solved it by using + 1 on my end date as well.