A Complex Date Command?

[BruceM]

My mistake. I thought you intended to run the code to see if a birthday is
coming up, but apparently you are running the code, then performing some
other action to see if the "birthday" is coming up. You're definitely
taking the long way around, but that's your choice. Why you would obtain
the MerchantDOB and then obliterate it is beyond me, but it's your project.
Good luck.

 

[Curtis Stevens]

I need the data like that as I do a lot with it. I have it setup so I go
through my forms, see which birthday cards that need to be mailed, which go
out 7 days before the day of or send out emails on the day of. I also use
the data to know who gets the cards - to stuff the envelopes & print out
labels for the month before, to get it all prepared, etc.

Are you saying I could just do the same thing on those tasks, have it setup
so it says if their bday is exactly 7 days from now, etc? I use queries to
run those tasks. I run it every day to see which ones get emailed and
everyday to see which cards get mailed, which is done 7 days before their
bday. The only other query or task is I run it once a mo for all bdays in
the upcoming month. Am I doing it the hard way?

Curtis

 

[John Vinson]

Am I doing it the hard way?

Yes, you are.

Your queries can use a CALCULATED FIELD based on the DOB:

HappyHappy: DateSerial(Year(Date()) + IIF(Format([DOB], "mmdd") <
Format(Date(), "mmdd"), 1, 0), Month([DOB]), Day([DOB]))

to dynamically calculate the next birthday for each record in the
table. This calculated date field can be used ANYWHERE that you would
use the stored birthday field.

John W. Vinson[MVP]

 

[BruceM]

Ever since I offered an alternative method (essentially the same as John
Vinson's) yesterday I have been saying that you are doing this the hard way.
I figured that you were deep into your current method, and that you regarded
it as too much trouble to change horses. The effort to keep up with
birthdays is admirable (I should do as well within my own circle of family
and friends), so I figured that streamlining the process while maintaining
data integrity would be time well spent.

 

[Mari]

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-