Last Date in a given Date Field

DoneganF

Registered User.
Local time
Today, 12:53
Joined
Aug 7, 2012
Messages
29
So I need to return the last day of a field called [Period]. If the value of the field is #05/14/2018#, I want to return #05/31/2018#. I created the following public function;

Public Function LastDayOfMonth(D As Date) As Date
Dim LDOM As Date
Dim NM As Date

NM = DateAdd("m", 1, D)
LDOM = DateSerial(Year(NM), Month(NM), "1")
LDOM = DateAdd("d", -1, LDOM)

LastDayOfMonth = LDOM


End Function

When I try to use it in a query ...

LDOM:LastDayOfMonth([Period])

I get an error message "Undefined function 'LastDayOfMonth' in expression.

I'm thinking I've either 'parked' the code in the wrong location, or I'm not calling it correctly. I appreciate any help!!
 
The code should be in a module:

Create -> Module
 
The code is in a module.
 
I would not call a user defined function for this, I would use the VBA.DateSerial() function as follows...
Code:
LDOM:DateSerial(Year(Period), Month(Period) + 1, 0)
hth
Mark
 
The function name must not be the same as the module name.
Mark
 
Ahh, thanks - that was my next question. Thanks again, this does now work as expected. Much appreciated!:)
 
On a side note, for the love of the Flying Spaghetti Monster, turn on Option Explicit!

Not using that has an AMAZING tendency to lead to any number of difficult-to-troubleshoot issues as variables get misnamed.
 
FYI, I'd also look at what you are naming things.

If you have global functions, I'd call the module they are in "GlobalFunctions". That way you can get in the habit of having the same module in all applications without trying to remember WHICH module in WHAT app you put a function.
 

Users who are viewing this thread

Back
Top Bottom