Karen:
I'd suggest that you use an auxiliary 'calendar' table. This is simply a
table of all dates within the period of time your database is likely to cover
(though its simple to extend it later). As well as the date column, CalDate,
you include other columns which hold information about each date. In your
case you'd have a column AcctgMonth. You can use a text data type , with
values January, February etc or an integer number data type with values 1,2
etc, whichever you prefer. The latter allows for sorting in month order and
its easy to return the name of the month from a number with Format("2000/" &
Format(AcctMonth,"00"),"mmmm"), so I'd suggest using an integer number data
type. The choice of year 2000 is entirely arbitrary here BTW, any year would
do.
An easy way to create calendar table is to serially fill down a column in
Excel with dates and then import it into Access as a table. Then define the
CalDate column as the primary key and add the other columns. You can also
do it by running a VBA procedure, which I'll give you at the end of this post.
Once you have your Calendar table with a populated CalDate column and an
empty AcctgMonth column you can fill the latter with a series of simple
update queries, e.g. for July 2008:
UPDATE Calendar
Set AcctgMonth = 7
WHERE CalDate BETWEEN
#06/29/2008# AND #08/02/2008#;
You don't need to save these, just paste the above into a new query in SQL
view and run it. Then change the SQL for another month and run that, and so
on. You don't need to update all the rows into the future, only as far as
you need to at present. You can run more update queries later to fill the
future dates' rows.
You might need another column AcctYear if the January and December
accounting months don't star/end at 1 January and 31 December. You'd fill
this column in a similar way with update queries, but you only need one per
year of course.
Armed with this table you can now join it to your main table, which I'll
call Transactions for this example, to return the accounting month for each
row, e.g. to return rows for accounting months May, June and July 2008:
SELECT Transactions.*, Calendar.AcctYear, Calendar.AcctMonth,
FORMAT("2000/" & Format(AcctMonth,"00"),"mmmm") AS AcctMonthName
FROM Transactions INNER JOIN Calendar
ON Transactions.Transactiondate = Calendar.CalDate
WHERE Calendar.AcctYear = 2008 AND Calendar.AcctMonth IN (5,6,7);
Finally here's the function to create the basic calendar table:
Public Function MakeCalendar_DAO(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)
' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)
Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long
Set dbs = CurrentDb
' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tdf = dbs.TableDefs(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
dbs.Execute strSQL
Else
Exit Function
End If
End If
On Error GoTo 0
' create new table
strSQL = "CREATE TABLE " & strTable & _
"(CalDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (CalDate ))"
dbs.Execute strSQL
' refresh database window
Application.RefreshDatabaseWindow
If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(CalDate ) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
dbs.Execute strSQL
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(CalDate ) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
dbs.Execute strSQL
End If
Next varDay
Next dtmDate
End If
End Function
Just paste the above into any standard module in your database. Say you
want to create a calendar for 2008 – 2010 you'd call it like so:
MakeCalendar "Calendar", #01/01/2008#, #12/31/2010#, 0
You can enter this in the debug window (aka immediate window) which you can
open by pressing Ctrl + G.
This will create the table with the just the one column CalDate . You can
then amend its design to add the AcctgMonth column and update it as described
above.
Ken Sheridan
Stafford, England
KarenY said:
I want to run a query for the monthly data. The table only supplies me the
date (no month). Our Accounting Month does not work along with the Calendar
Month, for instance, July 2008 starts with 29th June and ends with 2nd August
2008.
I can make the month either with PLUS day or MINUS day for the month across
with 2 months. which does not include the dates of 3 months :
I have a criteria for pop up parameter value for the user to input the
month, e.g. July,
then I made in the "field" with another parameter pops up to fill out 1
(i.e. plus 1 day). It works out fine to have June included.
Since this July includes the 1st 2 days in August, I don't know whether it's
poslsible to combine the "minus" in the following function :
AcctgMonth: Format$([Date]+[Enter Plus days betw MEC and cal-month, if no
diff, enter 0],'mmmm')
Please help.
thanks
Karen
