Forum Discussion
Solved
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
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.
- 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.
