Date

[dkalra1981]

I have 2 dates columns.

Column A & Column B

I need run the query and need show the number of days whose difference (A-B) is greater than 5.

Currently, I'm using DateDiff('d',[Date1],[Date2],Date()). By using this, I'm getting the difference of all the dates and their records. But I only needs the records in which the difference is more than 5 days old.

Can anyone please help!!!

Many thanks

D

 

[boblarson]

Your use of the DateDiff has too many arguments anyway. But you would just put this as a column in your query:

NumberOfDays:DateDiff('d',[Date1],[Date2])

and then in the criteria you put

> 5

 

[dkalra1981]

Thanks for the quick reply!!

Can we exclude the weekends from it??

Many Thanks

D

 

[boblarson]

That's a bit more complex so you would need something like this function in order to make it work (instead of the DateDiff).

 

[dkalra1981]

Hi Bob

Thanks for the reply!!!

Can I ask you where should I enter the code?

Public Function fGetWorkdays2(pstart As Date, pend As Date) As Integer
'************************************************
'Purpose: Compute number of workdays (Mon - Fri)
' between pStart and pEnd
're: http://www.access-programmers.co.uk/forums/showthread.php?t=164254&highlight=simplified
'Coded by: raskew
'Input: ? fGetWorkdays2(#2/10/06#, #7/13/06#)
'Output: 110
'************************************************

fGetWorkdays2 = 7 - WeekDay(pstart) + 5 * (DateDiff("ww", pstart, pend) - 1) + WeekDay(pend) - 1

End Function


Thanks

Deepak

 

[boblarson]

Same as the datediff in the query:

NumberOfDays:fGetWorkdays2([Date1], [Date2])