A Complex Date Command?

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" wrote in message
...
I just changed my system clock to 1/2/06 and tried 12/29/2000 and did it,
it
changed to 12/29/2006, which is what I wanted.

???


I'm saying that if a merchant's birthday is on December 29 and you run
the
code on December 31, 12/29 12/31. However, if you run the code on
January
2, 12/29 1/2. That's how Access will evaluate a date consisting of
only
month and day.
My point in the posts I have made is that it seems to me you are using a
complex and somewhat unreliable method to solve the problem.
My point about CDate is that the function is used if a date is stored in
a
text field, and you need to evaluate the value (to see if it is in the
future, or whatever). Assuming that your MerchantDOB field is a date
field,
both that field and the Date function are dates, so there is no need to
use
CDate to convert them to date values.

"Curtis Stevens" wrote in
message
...
Sorry, I don't follow you, I tested this out, moved my clock to Jan &
still
works fine. I'm simply using this code to convert the YR in their DOB
to
the
yr it needs to be. A new record is added & it has their actual DOB yr
and
I
need that changed to this or next yr according to when it falls per
today's
date. That's all I'm using it for.

Curtis


If the merchant has a birthday late in the year and you run the code
in
the
new year you will get the exact opposite result from what you expect.
Also,
it seems rather roundabout to use a type conversion function (CDate)
when
you already have date fields and functions. Since you aren't
formatting
the
CDate result you will be using your system settings for the display,
in
which case DateSerial would be more direct:
DateSerial(Year(Date()),Month(Me.MerchantDOB),Day( Me.MerchantDOB))
You could wrap a format around that, if you wish.

"Curtis Stevens" wrote in
message
...
I'm amazed I figured it out, but I found out what the problem was,
here
is
the code that works!!!!

Thanks Damian!

Me.MerchantDOB = IIf(Format(Me.MerchantDOB, "mm/dd") = Format(Date,
"mm/dd"), CDate(Year(Date) + 1 & "/" & Month(Me.MerchantDOB) & "/" &
Day(Me.MerchantDOB)), CDate(Year(Date) & "/" & Month(Me.MerchantDOB)
&
"/"
&
Day(Me.MerchantDOB)))

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


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" wrote in message
...
I just changed my system clock to 1/2/06 and tried 12/29/2000 and did it,
it
changed to 12/29/2006, which is what I wanted.

???


I'm saying that if a merchant's birthday is on December 29 and you run
the
code on December 31, 12/29 12/31. However, if you run the code on
January
2, 12/29 1/2. That's how Access will evaluate a date consisting of
only
month and day.
My point in the posts I have made is that it seems to me you are using a
complex and somewhat unreliable method to solve the problem.
My point about CDate is that the function is used if a date is stored in
a
text field, and you need to evaluate the value (to see if it is in the
future, or whatever). Assuming that your MerchantDOB field is a date
field,
both that field and the Date function are dates, so there is no need to
use
CDate to convert them to date values.

"Curtis Stevens" wrote in
message
...
Sorry, I don't follow you, I tested this out, moved my clock to Jan &
still
works fine. I'm simply using this code to convert the YR in their DOB
to
the
yr it needs to be. A new record is added & it has their actual DOB yr
and
I
need that changed to this or next yr according to when it falls per
today's
date. That's all I'm using it for.

Curtis


If the merchant has a birthday late in the year and you run the code
in
the
new year you will get the exact opposite result from what you expect.
Also,
it seems rather roundabout to use a type conversion function (CDate)
when
you already have date fields and functions. Since you aren't
formatting
the
CDate result you will be using your system settings for the display,
in
which case DateSerial would be more direct:
DateSerial(Year(Date()),Month(Me.MerchantDOB),Day( Me.MerchantDOB))
You could wrap a format around that, if you wish.

"Curtis Stevens" wrote in
message
...
I'm amazed I figured it out, but I found out what the problem was,
here
is
the code that works!!!!

Thanks Damian!

Me.MerchantDOB = IIf(Format(Me.MerchantDOB, "mm/dd") = Format(Date,
"mm/dd"), CDate(Year(Date) + 1 & "/" & Month(Me.MerchantDOB) & "/" &
Day(Me.MerchantDOB)), CDate(Year(Date) & "/" & Month(Me.MerchantDOB)
&
"/"
&
Day(Me.MerchantDOB)))

On Fri, 3 Nov 2006 11:46:02 -0800, Curtis Stevens
wrote:

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]

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.

"Curtis Stevens" wrote in message
news
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


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" wrote in
message
...
I just changed my system clock to 1/2/06 and tried 12/29/2000 and did
it,
it
changed to 12/29/2006, which is what I wanted.

???


I'm saying that if a merchant's birthday is on December 29 and you run
the
code on December 31, 12/29 12/31. However, if you run the code on
January
2, 12/29 1/2. That's how Access will evaluate a date consisting of
only
month and day.
My point in the posts I have made is that it seems to me you are using
a
complex and somewhat unreliable method to solve the problem.
My point about CDate is that the function is used if a date is stored
in
a
text field, and you need to evaluate the value (to see if it is in the
future, or whatever). Assuming that your MerchantDOB field is a date
field,
both that field and the Date function are dates, so there is no need
to
use
CDate to convert them to date values.

"Curtis Stevens" wrote in
message
...
Sorry, I don't follow you, I tested this out, moved my clock to Jan
&
still
works fine. I'm simply using this code to convert the YR in their
DOB
to
the
yr it needs to be. A new record is added & it has their actual DOB
yr
and
I
need that changed to this or next yr according to when it falls per
today's
date. That's all I'm using it for.

Curtis


If the merchant has a birthday late in the year and you run the
code
in
the
new year you will get the exact opposite result from what you
expect.
Also,
it seems rather roundabout to use a type conversion function
(CDate)
when
you already have date fields and functions. Since you aren't
formatting
the
CDate result you will be using your system settings for the
display,
in
which case DateSerial would be more direct:
DateSerial(Year(Date()),Month(Me.MerchantDOB),Day( Me.MerchantDOB))
You could wrap a format around that, if you wish.

"Curtis Stevens" wrote in
message
...
I'm amazed I figured it out, but I found out what the problem
was,
here
is
the code that works!!!!

Thanks Damian!

Me.MerchantDOB = IIf(Format(Me.MerchantDOB, "mm/dd") =
Format(Date,
"mm/dd"), CDate(Year(Date) + 1 & "/" & Month(Me.MerchantDOB) &
"/" &
Day(Me.MerchantDOB)), CDate(Year(Date) & "/" &
Month(Me.MerchantDOB)
&
"/"
&
Day(Me.MerchantDOB)))

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(Mont h([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-