A Complex Date Command?

C

Curtis Stevens

a text box named - MerchantDOB
a command button named - CmdDOBAdj

MerchantDOB may have someone's dob, like 06/02/1981. I can't use that
format to be able to know when their bday is up, so I must convert it to
06/02/2007.

Here's the tricky part. When I click the cmd button, I want it to change
the dob in the text box according to what it is & what the current date is
(today's date).

For example, today's date is 11/1/06. If their DOB is 1/1/XX to 11/1/XX
then I want it to change it to XX/XX/2007. But if it is 11/2/XX to 12/31/XX,
then change it to XX/XX/2006. And this range would change accordinly with
the current day's date.

The only part that changes is the yr, from their DOB yr to 2006 or 2007. I
then up it by one yr with another cmd button on the day of their bday so it
comes around again next yr and so on.

Is this too complicated?

Thanks
Curtis
 
D

Damian S

Hi Curtis,

You will lose data if you overwrite the DOB with a new date. I would
consider doing it another way. Be that as it may, using code similar to this
will achieve what you are after:

me.MerchantDOB = iif(me.MerchantDOB <= date(), cdate(year(date) + 1 & "/" &
month(me.MerchantDOB) & "/" & day(me.MerchantDOB)), me.MerchantDOB)

Damian.
 
C

Curtis Stevens

Hi Damian,

I tried this, but it is looking for more code, if statement not done? Can't
figure this out, little beyond my head.

Private Sub CmdDOBCopy_Click()
me.MerchantDOB = if(me.MerchantDOB <= date(), cdate(year(date) + 1 & "/" &
month(me.MerchantDOB) & "/" & day(me.MerchantDOB)), me.MerchantDOB)
End Sub
 
D

David F Cox

apologies if I am missing the plot.

To me it looks as simple as:
If Format([dob],"mm:dd") = Format(date(),"mm:dd") then ' its a birthday!
 
C

Curtis Stevens

I thought it was giving me an error.

I tried this, seems to work for any date range that needs to go to 2007, but
any thing from tomorrows date until the end of the yr is changing to 2007 and
not 06.

Curtis
 
D

Damian S

I reckon that's what he should be doing, but that's not what he specifically
asked...

David F Cox said:
apologies if I am missing the plot.

To me it looks as simple as:
If Format([dob],"mm:dd") = Format(date(),"mm:dd") then ' its a birthday!


Curtis Stevens said:
a text box named - MerchantDOB
a command button named - CmdDOBAdj

MerchantDOB may have someone's dob, like 06/02/1981. I can't use that
format to be able to know when their bday is up, so I must convert it to
06/02/2007.

Here's the tricky part. When I click the cmd button, I want it to change
the dob in the text box according to what it is & what the current date is
(today's date).

For example, today's date is 11/1/06. If their DOB is 1/1/XX to 11/1/XX
then I want it to change it to XX/XX/2007. But if it is 11/2/XX to
12/31/XX,
then change it to XX/XX/2006. And this range would change accordinly with
the current day's date.

The only part that changes is the yr, from their DOB yr to 2006 or 2007.
I
then up it by one yr with another cmd button on the day of their bday so
it
comes around again next yr and so on.

Is this too complicated?

Thanks
Curtis
Click to expand...
Click to expand...
 
C

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.

That's why I need the data this way, do a lot of stuff with it!

Curtis
 
D

Damian S

No worries... Use this one instead:

me.MerchantDOB = iif(format(me.MerchantDOB, "yyyy/mm") <= format(date(),
"yyyy/mm"), cdate(year(date) + 1 & "/" &
month(me.MerchantDOB) & "/" & day(me.MerchantDOB)), cdate(year(date) & "/" &
month(me.MerchantDOB) & "/" & day(me.MerchantDOB)))

Damian.
 
C

Curtis Stevens

I pasted this and same thing?
No worries... Use this one instead:

me.MerchantDOB = iif(format(me.MerchantDOB, "yyyy/mm") <= format(date(),
"yyyy/mm"), cdate(year(date) + 1 & "/" &
month(me.MerchantDOB) & "/" & day(me.MerchantDOB)), cdate(year(date) & "/" &
month(me.MerchantDOB) & "/" & day(me.MerchantDOB)))

Damian.
Click to expand...
 
D

Damian S

What do you mean the same thing? Have you tried it for items where the
MerchantDOB is after today?

D.
 
D

David F Cox

Ok - apologies for butting in. I generally find that by asking "stupid"
questions I end up being less stupid, and sometimes "Is it plugged in?"
results in a long silence ....
 
B

BruceM

I have not been following this discussion all that closely, but one
possibility so that you don't need to change the DOB is to add a calculated
field to a query:
DateCalc: DateSerial(Year(Date()),Month([MerchantDOB]),Day([MerchantDOB]))
Then use DateCalc in expressions or code:
If DateCalc = Date Then
MsgBox "Birthday Today"
End If

or

If DateCalc + 7 = Date Then
MsgBox "Birthday in exactly one week"
End If

You can look for date ranges in a parameter query or otherwise treat it as a
date field for most purposes. Saves you having to change a value that
shouldn't be changed, and simplifies things quite a bit.
 
C

Curtis Stevens

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)))
 
B

BruceM

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.
 
C

Curtis Stevens

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
 
B

BruceM

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.
 
C

Curtis Stevens

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.

???
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Command Button - Increase Data by One 2
part numbers auto-convert to dates 4
Date Format Changes 0
If Statement for analyzing dates 0
Need magic Excel formula for Date calculation 2
Reverse Index Match 2
Please help with formula (multiple ifs to determine due date) 3
Date Series into new Table 1

Top