last full month 2

[PAULCALLAGHAN]

I'm using Access 2000 and trying to create a query to get data from a table based on the last full month. It will drive a report that will be run every month, at the beginning of the month.

That is, the user will run this during the first week of November to get data for October.

Is there a specific function that will do this? (Crystal Report Writer has this.) If not, can someone please give me guidance on how to build one?

 

[BobStubbs]

This function will return either the first or last date in the previous full month. Put it in a global module.:

Public Function FindLastMonth(MonthStartFlag As Boolean) As Date

Dim dtMonth As Date
Dim stMyDate As String

'------------------------------------------------------
'- Get the first day of the current month as a string -
'------------------------------------------------------
stMyDate = "01/" & Format$(Date, "mm/yyyy")

'------------------------------------------------------
'- Subtract 1 (day) from this date, and store in date -
'- variable                                           -
'------------------------------------------------------
dtMonth = CDate(stMyDate) - 1

'------------------------------------------------------
'- To find the first day of the previous month,       -
'- repeat the process                                 -
'------------------------------------------------------
If MonthStartFlag = True Then
    stMyDate = "01/" & Format$(dtMonth, "mm/yyyy")
    dtMonth = CDate(stMyDate)
End If

'------------------------------------------------------
'- Return the required value as a date                -
'------------------------------------------------------
FindLastMonth = dtMonth

End Function

Pass the parameter 'True' to get the first day in the month; 'False' to get the last day in the month

Use the function in the Criteria field for the date in your query, like this:

Between FindLastMonth(True) And FindLastMonth(False)

This will select all records where the date field contains a value between the first and last day in the previous month.


Bob Stubbs

 

[PAULCALLAGHAN]

Thanks Bob, that's exactly what I'm looking for.

I'm a bit of a novice though. What do you mean by "place in a global module"?

 

[BobStubbs]

Open the Visual Basic Editor, via the Tools ... Macro ... Visual Basic Editor menu option.

Choose View ... Project Explorer from the Visual Basic menu.

In the left-hand pane you should see a folder called 'Modules'. Click The + sign to expand this. There should be a file called Module1.

Double-click this to open it. The right pane now shows VBA code within this module. This is where you should paste the function.

It's called a 'global module' because procedures and functions written here, can be called from anywhere in your database. That's what you need here ... you want to be able to use the FindLastMonth function anywhere it's useful.

In particular, functions placed in a global module can be used in queries, as I showed in my first example.

I hope that this helps.


Bob Stubbs