Forum Discussion
DateDiff with a Where Clause
Hello Experts,
I am trying to use a DateDiff to find the day count between 2 dates.
I need a Where Clause (blue part)
I am getting a #error though.
I am not sure if the where clause below is accurate?
I am not sure if i can use a where clause with a "datediff" like I am doing.
DayCountDD: Abs(DateDiff("d",[qryDrawsDecliningCumDrawn].[FundingDate],"ID=" & [qryDrawsDecliningCumDrawn].[ID] & " And [FirstOfIDDetailpk] < " & [qryDrawsDecliningCumDrawn].[FirstOfIDDetailpk],[DayLastBdMo]))
My original was like this and it does work (no #error) but the count is off since I dont have a where clause.
DayCountDD: Abs(DateDiff("d",[qryDrawsDecliningCumDrawn].[FundingDate],[DayLastBdMo]))
Any thoughts are appreciated.
thank you.
Hi,
No matter how you formulate your WHERE statement, the DateDiff function does not have a WHERE part/argument. You therefore need a different approach.
Perhaps a DLookUp with WHERE argument would help you to determine the first comparison value, but that would not be very performant.
For more specific help, we would need a more detailed description.
Servus
Karl
****************
Access Forever News DevCon
Access-Entwickler-Konferenz AEK - 19./20.10. Nürnberg
- It's harder to offer suggestions on a partial SQL statement. Perhaps you can post the entire SQL from the query. The part that is shown doesn't appear to be valid SQL, but in isolation, I might be missing something. Rather than guess wrong, though, I'd rather see the entire query.
Thank you. Hi,
No matter how you formulate your WHERE statement, the DateDiff function does not have a WHERE part/argument. You therefore need a different approach.
Perhaps a DLookUp with WHERE argument would help you to determine the first comparison value, but that would not be very performant.
For more specific help, we would need a more detailed description.
Servus
Karl
****************
Access Forever News DevCon
Access-Entwickler-Konferenz AEK - 19./20.10. Nürnbergthank you.
I have since then put together bits and pieces from a previous expert's response on a separate question and the following is what I came up with and it seems to give me what I need:
SELECT D1.*, NZ((SELECT MIN(FundingDate)
FROM tblDraws AS D2
WHERE D2.ProjID = D1.ProjID
AND D2.Type = D1.Type
AND D2.FundingDate > D1.FundingDate)-FundingDate,0) AS DayCount
FROM tblDraws AS D1
ORDER BY ProjID, Type, FundingDate;Nested queries are hard for me to follow but I am getting better at them.
thank you for the response guys.
