Developer Forum

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:

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:

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