Query all dates between a Start Date and End Date

[callaway]

I need to be able to query and count the number of days between a start and end date based on a specified criteria. For example, if someone entering a program has a start date of 10/29/2001 and an end date of 11/03/2001, there are obviously 6 total days, but how do I calculate only the number of days in October. (The only dates in the query/table are the start and end dates.)

 

[pcdaveh]

There are a couple of ways of doing this. One is by using datepart function but it is kind of quirky and forcing things a bit. It is also kind of nasty looking. But if you don't know code then here is a substitute:

Assumption query base on a table that has a field called Date: The calculated field is called monthend:

MonthEnd: IIf((CVDate(IIf(DatePart("m",[Date])+1=13,1,DatePart("m",[Date])+1) & "/" & 1 & "/" & DatePart("yyyy",[date])))-1=#12/31/00#,#12/31/01#,(CVDate(IIf(DatePart("m",[Date])+1=13,1,DatePart("m",[Date])+1) & "/" & 1 & "/" & DatePart("yyyy",[date])))-1)

What I would term to be a better solution is using a module and calculating this

Function LastOfMonth(Optional dteDate As Date) As Date ' This function calculates the last day of a month, given a date.
' If no date is passed in, the function uses the current date.

If CLng(dteDate) = 0 Then
dteDate = Date
End If

' Find the first day of the next month, then subtract one day.
LastOfMonth = DateSerial(Year(dteDate), Month(dteDate) + 1, 1) - 1
End Function

There you go! I'm sure there are other solutions. Just something to chew on.

Later!

 

[callaway]

I'll give it a shot, thanks.