Thanks.

But how do i calculate how many of those days occurred in October, how many

in November, and how many in December?

n this specific case you'd need to create a custom function. Below is one I

threw together rapidly to try to help you out. It is not the most elegant

way to do this but it does work. Feel free to improve upon it.

Function NoDaysByMonth(dtStart As Date, dtEnd As Date) As String

On Error GoTo Error_Handler

Dim iNoMonths As Integer

Dim dtFirstofMonth As Date

Dim dtLastOfMonth As Date

iNoMonths = DateDiff("m", dtStart, dtEnd) 'No months between the date

'Dates are in the same month

If iNoMonths = 0 Then

NoDaysByMonth = DateDiff("d", dtStart, dtEnd) + 1 & " days for " &

Format(dtStart, "mmmm, yyyy")

Exit Function

End If

'Dates are in different months

NoDaysByMonth = DateDiff("d", dtStart, DateAdd("m", 1,

DateSerial(Year(dtStart), _

Month(dtStart), 1)) - 1) + 1 & " days for " &

Format(dtStart, "mmmm, yyyy")

For i = 1 To iNoMonths - 1

dtFirstofMonth = DateAdd("m", i, dtStart)

dtFirstofMonth = DateSerial(Year(dtFirstofMonth),

Month(dtFirstofMonth), 1)

dtLastOfMonth = DateAdd("m", i, dtStart)

dtLastOfMonth = DateAdd("m", 1, DateSerial(Year(dtLastOfMonth),

Month(dtLastOfMonth), 1)) - 1

NoDaysByMonth = NoDaysByMonth & vbCrLf & _

DateDiff("d", dtFirstofMonth, dtLastOfMonth) + 1 & "

days for " & _

Format(dtLastOfMonth, "mmmm, yyyy")

Next i

NoDaysByMonth = NoDaysByMonth & vbCrLf & DateDiff("d",

DateSerial(Year(dtEnd), _

Month(dtEnd), 1), dtEnd) + 1 & " days for " &

Format(dtEnd, "mmmm, yyyy")

Exit Function

Error_Handler:

MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &

"Error Number: " & _

Err.Number & vbCrLf & "Error Source: NoDaysByMonth" & vbCrLf & "Error

Description: " & _

Err.Description, vbCritical, "An Error has Occured!"

Exit Function

End Function

--

Hope this helps,

Daniel Pineault

http://www.cardaconsultants.com/

For Access Tips and Examples: http://www.devhut.net

Please rate this post using the vote buttons if it was helpful.

"PF" wrote:

threw together rapidly to try to help you out. It is not the most elegant

way to do this but it does work. Feel free to improve upon it.

Function NoDaysByMonth(dtStart As Date, dtEnd As Date) As String

On Error GoTo Error_Handler

Dim iNoMonths As Integer

Dim dtFirstofMonth As Date

Dim dtLastOfMonth As Date

iNoMonths = DateDiff("m", dtStart, dtEnd) 'No months between the date

'Dates are in the same month

If iNoMonths = 0 Then

NoDaysByMonth = DateDiff("d", dtStart, dtEnd) + 1 & " days for " &

Format(dtStart, "mmmm, yyyy")

Exit Function

End If

'Dates are in different months

NoDaysByMonth = DateDiff("d", dtStart, DateAdd("m", 1,

DateSerial(Year(dtStart), _

Month(dtStart), 1)) - 1) + 1 & " days for " &

Format(dtStart, "mmmm, yyyy")

For i = 1 To iNoMonths - 1

dtFirstofMonth = DateAdd("m", i, dtStart)

dtFirstofMonth = DateSerial(Year(dtFirstofMonth),

Month(dtFirstofMonth), 1)

dtLastOfMonth = DateAdd("m", i, dtStart)

dtLastOfMonth = DateAdd("m", 1, DateSerial(Year(dtLastOfMonth),

Month(dtLastOfMonth), 1)) - 1

NoDaysByMonth = NoDaysByMonth & vbCrLf & _

DateDiff("d", dtFirstofMonth, dtLastOfMonth) + 1 & "

days for " & _

Format(dtLastOfMonth, "mmmm, yyyy")

Next i

NoDaysByMonth = NoDaysByMonth & vbCrLf & DateDiff("d",

DateSerial(Year(dtEnd), _

Month(dtEnd), 1), dtEnd) + 1 & " days for " &

Format(dtEnd, "mmmm, yyyy")

Exit Function

Error_Handler:

MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &

"Error Number: " & _

Err.Number & vbCrLf & "Error Source: NoDaysByMonth" & vbCrLf & "Error

Description: " & _

Err.Description, vbCritical, "An Error has Occured!"

Exit Function

End Function

--

Hope this helps,

Daniel Pineault

http://www.cardaconsultants.com/

For Access Tips and Examples: http://www.devhut.net

Please rate this post using the vote buttons if it was helpful.

"PF" wrote:

hanks - I'm very much a VB novice - but I think i get the gist and think

this will help me a lot.

"Daniel Pineault" wrote:

this will help me a lot.

"Daniel Pineault" wrote:

Another way to get at this would be to create a query.

1. To do so, you would need a table (tbl_Numbers) with one field

(intNumber) with 10 records (values from 0 to 9). I use this table for lots

of different purposes, but generating date sequences is the most frequent.

2. Then, you need a query (qry_Numbers_to_99) which generates a sequence of

numbers from 0 to some other value (in your case you probably only need < 100

days in your result set). It would look like:

SELECT Tens.IntNumber * 10 + Ones.intNumber

FROM tbl_Numbers as Tens, tbl_Numbers as Ones

3. Finally, you need the query (qry_Billing_Days) that returns the billing

year, month, and the number of days in each of those months.

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;

SELECT Year(DateAdd("d",[intNumber],[StartDate])) AS BillYear,

Month(DateAdd("d",[intNumber],[StartDate])) AS BillMonth,

Count(IntNumber) AS Days

FROM qry_Numbers_to_99

WHERE (((DateAdd("d",[intNumber],[StartDate]))<=[EndDate]))

GROUP BY Year(DateAdd("d",[intNumber],[StartDate])),

Month(DateAdd("d",[intNumber],[StartDate]));

----

HTH

Dale

1. To do so, you would need a table (tbl_Numbers) with one field

(intNumber) with 10 records (values from 0 to 9). I use this table for lots

of different purposes, but generating date sequences is the most frequent.

2. Then, you need a query (qry_Numbers_to_99) which generates a sequence of

numbers from 0 to some other value (in your case you probably only need < 100

days in your result set). It would look like:

SELECT Tens.IntNumber * 10 + Ones.intNumber

FROM tbl_Numbers as Tens, tbl_Numbers as Ones

3. Finally, you need the query (qry_Billing_Days) that returns the billing

year, month, and the number of days in each of those months.

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;

SELECT Year(DateAdd("d",[intNumber],[StartDate])) AS BillYear,

Month(DateAdd("d",[intNumber],[StartDate])) AS BillMonth,

Count(IntNumber) AS Days

FROM qry_Numbers_to_99

WHERE (((DateAdd("d",[intNumber],[StartDate]))<=[EndDate]))

GROUP BY Year(DateAdd("d",[intNumber],[StartDate])),

Month(DateAdd("d",[intNumber],[StartDate]));

----

HTH

Dale

1. calculating number of days belonging to each month within a given

2. calculating number of days belonging to each month within a given week

3. Calculate the first day of the month for the current month?

4. Get day name given year, month and day number

5. Fuction that gives number of days of a given month

6. Calculate the number of months between dates include partial month

7. Number of days in month counted from shortened name of month &

8. Number of days in month counted from shortened name of month & yea

9. Avg. 1-month, 3-month, 6-month & 12-month Stock Returns

10. How do I calculate how many days in which months given two dates .

11. hOW DO I CHANGE THE FROM MONTH/DAY/YEAR TO DAY/MONTH/YEAR?

12. How to calculate nr of days of 2 given months.

13. Match Last day of this month to last day of last month

14. Date order (month day year vs day month year)

15. month view - can i see month on every day, not just first day of m

4 post • Page:**1** of **1**