Formula for Returning End of Year date

[golferbabe38]

I've tried every dateadd ect. function I can find/think of and I'm just not getting anywhere. In the query, I would like to have the cell return the end of year date (12/31/##) based off of the date in the previous cell:
Ending date: [Class date]+..something...
If the class date is anything from 01-01-1996 of 05-31-2007 I want the Ending date to return 12-31-1996 and 12-31-2007 respectively.
Not sure if this is an option. Thanks for any info.

 

[dmonney]

try this

dim endofyear as date
endofyear = Format("12/31/"&Format(davevalue,"yyyy"),"mm/dd/yyyy")

 

[namliam]

I hate to be a nit picker dmonney, but that is relying on implicit conversion... which is a bad idea... anything implicit will rely on access thinking and using regional settings and what not to 'think' what it needs to do. Letting any M$ product do such thinking is *not good*

endofyear = Dateserial (year(datevalue), 12,31)

would not rely on the implicit conversion and do the same.