Display the last day of a Half (1st and 2nd or 3rd and 4th Quarter) based on Date

[padlocked17]

All,

I'm trying to create an expression that evaluates the date in the ACC_DATE field and then displays the last day of either the 2nd or 4th quarter of the year based on whether or not the date in ACC_DATE is in the 1st or 2nd quarter (Would display last day in 2nd quarter) or 3rd or 4th quarter (Would display the last day in 4th quarter)

Basically I'm looking for a Semi Annual type function that displays the last day in a half of a year based on the input date.

Where I get stuck is:

IIf(DatePart("q",[ACC_DATE])<3, , )

I know I can get the last day of the quarter by using the following, but I want the last day of the Semi Annual:

DateSerial(Year([ACC_DATE]), Int((Month([ACC_DATE]) - 1) / 3) * 3 + 4, 0)

Is there a better way of doing this?

 

[pbaldy]

It seems like you don't really need to do any math. If the quarter is less than 3, the date is 6/30, else 12/31, in either case getting the year from the field. Or have I misunderstood?

 

[padlocked17]

No I feel like I should say "Here's my Sign".

I really do love to make things harder for myself than I need to sometimes.

So I guess the following should work:

IIf(DatePart("q",[ACC_DATE])<3, "6/30/" & DatePart("yyyy", [ACC_DATE]), "12/31/"& DatePart("yyyy", [ACC_DATE]))

But is there a better way of creating it so that Access recognizes it as a date?

 

[pbaldy]

We all have our "duh" moments, though somebody must be short a few because I have more than my fair share.

You can still use the DateSerial function, just hard code the month and day arguments and use the Year() function to get the year from the field. Or you could wrap your string in the CDate() function.

 

[padlocked17]

Amazing as always. Thanks for the help.

The finished product for the archives:

GeneratedDue: IIf(DatePart("q",[ACC_DT])<3, CDate("6/30/" & Year([ACC_DT])), CDate("12/31/"& Year([ACC_DT])))

 

[pbaldy]

AirForceDad is always happy to help AirForceRuss!

 

[namliam]

Just some different options, cause I love month things like this:
dateadd("M",iif(month([ACC_DT]) <= 6,-6,0),#12/31/2010#)
CDate ( IIf(DatePart("q",[ACC_DT])<3, "6/30/" , "12/31/") & Year([ACC_DT]) )

And going from the function
dateadd("M", 1 , date()- day( date()) + 1 )
Which will calculate the last day of any given date... I can not really find a 'really nice one' at the moment but...
Dateadd("M", IIf(Month(date() ) <= 6, 6, 12) - month( date() ) + 1, date()- day( date() ) + 1 ) - 1

They arent better in any way shape or form I dont think, but just fun for practice