# Thread: End Of Month query as a function (2k/XP)

1. ## End Of Month query as a function (2k/XP)

This is probably elementary for most of you, so please forgive my simple mindedness. I have a query that calculates the end of month for various reports and queries. However, whenever I use it in a query, I always have to substitute the date field in two places. Is there a way to create a function that could be called from any place in the database to calculate the end of month requested?

SELECT DateSerial(Year([dtmDate]),Month([dtmDate])+1,1)-1 AS EOM
FROM MyTable;

As usual, thanks for your ideas.

Ken

2. ## Re: End Of Month query as a function (2k/XP)

You could create a function that accepted a date argument and return an EOM date, then just call that function in a query or wherever.

<pre>Public Function EOMDate(ByVal dtmDate As Date) As Date
Dim dtmReturn As Date

dtmReturn = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)
EOMDate = dtmReturn
End Function</pre>

The reason this works is because the end of month for the date passed in is the same as the zeroth day of the next month.

3. ## Re: End Of Month query as a function (2k/XP)

Create a new standard module, and copy the following code into it:

Public Function EndOfMonth(varDate As Variant) As Variant
If VarType(varDate) = vbDate Then
EndOfMonth = DateSerial(Year(varDate), Month(varDate) + 1, 0)
Else
EndOfMonth = Null
End If
End Function

Use like this:

SELECT EndOfMonth([dtmDate]) As EOM
FROM MyTable;

4. ## Re: End Of Month query as a function (2k/XP)

Charlotte,

You and Hans are incredible! Both approaches accomplished EXACTLY what I was seeking!

THANK YOU BOTH!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•