DateDif - End of Month

[Karin]

Hi,
I have a field named ProjectFYE, which is a date (12/31/08).
I need to calculate the 1st quarter end date and then add 45 days to it.
Then the same with 2nd quarter, and 3rd quarter. The results would be:
1Q=3/31/08 +45 = 5/15/08; 2Q=6/30/08+45=8/14/08; 3Q=9/30/08+45=11/14/08
(nothing due 4Q).
I know how to do this in Excel with EOMonth formula, but do not know how to
write it in Access. Can someone help? (Also, ProjectFYE is not always
12/31.) TIA.

 

[Klatuu]

Here is a function that will return either the first or last day of a
quarter:

For your needs, assume you want the 45 days after the first quarter of the
current year:

=DateAdd("d", 45, QuarterDate(1,Year(Date), True))

'---------------------------------------------------------------------------------------
' Procedure : QuarterDate
' DateTime : 1/15/2008 15:40
' Author : Dave Hargis
' Purpose : Return either the First Date for a quarter
' Arguments : lngQtr - The number of the quarter 1,2,3, or 4
' : lngYear - The year of the date to be returned
' : blnLastDay - If True, returns the last day of the quarter
' : If False, returns the first day of the quarter
' Returns : The First day of the quarter or the Last day of the quarter
depending
' : on the blnLastDay argument.
' : If lngQtr is < 1 or > 4, Null is returned
'---------------------------------------------------------------------------------------
'
Public Function QuarterDate(lngQtr As Long, lngYear As Long, blnLastDay As
Long) As Variant

On Error GoTo QuarterDate_Error

If lngQtr < 1 Or lngQtr > 4 Then
QuarterDate = Null
Else
If blnLastDay Then
QuarterDate = DateSerial(lngYear, Choose(lngQtr, 3, 6, 9, 12) +
1, 0)
Else
QuarterDate = DateSerial(lngYear, Choose(lngQtr, 1, 4, 7, 10),
1)
End If
End If

QuarterDate_Exit:
On Error GoTo 0

Exit Function

QuarterDate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure QuarterDate of Module modDateFunctions"
GoTo QuarterDate_Exit
End Function