Constructing a date

[NevilleT]

I am sure I have done this before but cannot work out how. I have a date
stored in dteStartDate. I want to calculate a date which is 16 quarters (4
years) after the quarter in which dteStartDate occurs.

Example. dteStartDate is 5 Jan 2009. Quarter starts on 1 Jan 2009 so the
date I am after is 31 Dec 2012

I have calculated which quarter the dteStartDate is in (1, 2, 3, 4) and
stored in lngQtr
I have tried the following without success. (I have only put in the first
quarter.)

If lngQtr = 1 Then
dteTemp = "#12/31/" & DateAdd("yyyy", 3, Year(dteStartDate)) & "#"

That causes an error.

I then tried:
If lngQtr = 1 Then
dteTemp = DateAdd("yyyy", Year(dteStartDate), #12:00:00 AM#)
dteTemp = DateAdd("m", 12, dteTemp)
dteTemp = DateAdd("d", 30, dteTemp)
End If

Used the # as I need to cater for 30/12/13 and 12/30/03. That put me in the
next century. I am sure there is a simple answer.

 

[NevilleT]

Sorted it out myself. Had forgotten to use DateSerial. Correct code is

If lngQtrOffset = 1 Then
dteTemp2 = DateSerial(Year(DateAdd("yyyy", 3, dteStartDate)), 12, 31)

 

[John Spencer]

DateAdd("q",16,dtestart) - Day(DateAdd("q",16,dteStart))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================