This thread helped me a lot. I needed to calculate the correct year for dates
already entered in a query. My requirements were a little different, and
thus my solution is a variation of the concept.
My situation was this:
I had a date field, [xfR_dtv]. But the dates in this field may or not have
the correct Year value.
I needed to convert all the dates in [xfR_dtv] to the same month and day,
while correcting the year.
Furthermore, the year would be based on the month of [xfR_dtv] in relation
to the current month. All dates in [xfR_dtv] should be roughly within 5
months going forward from today or within 4 months prior to today.
Therefore, I can calculate which year they should be based on these
guidelines. It gets tricky because valid month for my end value can be high
or low month numbers.
Here was my solution:
CorrectYearDate: Iif(((Month(Date()) between 1 AND 4) And
(Month([xfR_dtv])>=(Month(DateAdd('m',-4,(Date())))))),(CDate(Month([xfR_dtv])
& "/" & Day([xfR_dtv]) & "/" & Year(Date())-1)),Iif((((Month(Date()) between
8 AND 12) AND
(Month([xfR_dtv])<=(Month(DateAdd('m',5,(Date()))))))),(CDate(Month([xfR_dtv])
& "/" & Day([xfR_dtv]) & "/" & Year(Date())+1)),(CDate(Month([xfR_dtv]) & "/"
& Day([xfR_dtv]) & "/" & Year(Date())))))
I hope this can help SOMEONE even a little, as I have received much help
from this site! I have tested it out and it works great for me. But if
anyone sees an error, or a way to do it better, please let me know.
m-