# Thread: End of the Month

1. Registered User
Join Date
Feb 2004
Location
Alpine Califormia
Posts
1,798

## Unanswered: End of the Month

how would I make my dates calculate for the end of the month, some months have 28 days, 30, 31 days. This is the code i'm using and the field I'm basing it off of "=DateSerial(Year([FIRSTRENEWALDATE])+2,Month([FIRSTRENEWALDATE]),Day([FIRSTRENEWALDATE]))"

2. Registered User
Join Date
Apr 2004
Location
Sydney Australia
Posts
369
Originally Posted by desireemm
how would I make my dates calculate for the end of the month, some months have 28 days, 30, 31 days. This is the code i'm using and the field I'm basing it off of "=DateSerial(Year([FIRSTRENEWALDATE])+2,Month([FIRSTRENEWALDATE]),Day([FIRSTRENEWALDATE]))"
Are you looking to retrieve all records where the date is the end of the month?

Perhaps you could add two fields with one being TheDayay([FIRSTRENEWALDATE]) and the other being M:Month([FIRSTRENEWALDATE]) which will give you the day of the month and the month itself.

You could then do an IIf along the lines of IIf(([M] Like 3 Or [M] Like 12) And [TheDay] Like 31, etc.

The funny faced is popping in, must be the : before Day([FIRSTRENEWALDATE])
Last edited by Mike375; 05-20-04 at 15:14.

3. Stuck on my opinions...
Join Date
Nov 2003
Posts
1,487
Return the Last Day of the Month, the Beginning of a Quarter or End of a Quarter Based on a Specific Date

Access' Date Add and DateDiff functions generally fill most needs for date manipulation. However, there are a few instances where you need to return a date for comparison purposes which are not simple additive date result from a current date. Two examples are specifying the date for the end of the current month (e.g. perhaps for billing purposes), or gathering data which occurs, or is scheduled to occur based on whether it is after the beginning of a quarter, or before the end of a quarter. The following two functions provide the code to determine the end of the month from a specific date and the first or last day of any yearly quarter based on a specific date. (The second function below, providing the quarterly dates relys upon the end of the month function so both must be included in your module.) Here's the end of the month function:
Code:
```Function EOMonth (Anydate)
'------------------------------------------------------------------------
'Purpose:	Returns the last day of the month for the date specified
'Accespts:  A Date or Date Variable.
'Returns:	VarType Date
'------------------------------------------------------------------------
On Error GoTo Err_EOM
Dim NextMonth, EndofMonth
NextMonth = DateAdd("m", 1, Anydate)
EndofMonth = NextMonth - DatePart("d", NextMonth)

EOMonth = EndofMonth

Exit_EOM:
Exit Function
Err_EOM:
MsgBox "Error" & " " & Err & " " & Error\$
Resume Exit_EOM
End Function```
Here's the Function to determine the beginning date or ending date of any quarter base on a date supplied:

Code:
```Function BEQuarter (ByVal Anydate, BeginOrEnd As Boolean) As Variant
'-------------------------------------------------------------------
'Purpose:	 Returns the beginning or the end of a quarter
'Uses:		 EOMonth() Function
'Input:		AnyDate: A date value, use of #'s to signify a date when variable
'				from a query or the immediate window.
BeginOrEnd: 0 Finds Beginning of Quarter, -1 Finds End of Quarter
''Returns:	VarType Date
'-------------------------------------------------------------------
On Error GoTo Err_EOQ
If BeginOrEnd <> 0 And BeginOrEnd <> -1 Then
MsgBox "Error: BeginOrEnd must be 0 or -1"
GoTo Exit_EOQ
End If

Dim EndofQuarter, BeginofQuarter, PrevQuarter
Static MonthVar(12) As Integer

If MonthVar(12) = 0 Then
MonthVar(1) = 2
MonthVar(2) = 1
MonthVar(3) = 3
MonthVar(4) = 2
MonthVar(5) = 1
MonthVar(6) = 3
MonthVar(7) = 2
MonthVar(8) = 1
MonthVar(9) = 3
MonthVar(10) = 2
MonthVar(11) = 1
MonthVar(12) = 3
End If

Anydate = Anydate - DatePart("d", Anydate)
EndofQuarter = DateAdd("M", MonthVar(DatePart("M", Anydate)), Anydate)
EndofQuarter = EOMonth(EndofQuarter)

If DatePart("m", EndofQuarter) = 6 Then
BeginofQuarter = DateAdd("q", -1, EndofQuarter) + 2
Else
BeginofQuarter = DateAdd("q", -1, EndofQuarter) + 1
End If

If BeginOrEnd = -1 Then
BEQuarter = EndofQuarter
Else
BEQuarter = BeginofQuarter
End If

Exit_EOQ:
Exit Function
Err_EOQ:
MsgBox "Error" & " " & Err & " " & Error\$
Resume Exit_EOQ
End Function```
Hope this helps........

#### Posting Permissions

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