Another Date ?

K

Keypad

Hi,

I need a way to know a week in advance when a yearly membership will expire.
For example, if the membership start date was 04/17/2008 then I want to show
a message starting on 4/10/2009 that say's "Your membership will expire in 7
days".

I know it's asking a lot, but it would be really cool if the days remaining
would decrease as the week progresses, then on 4/10/2009 I could say
"Membership Expired".

The membership date is a text box named txtVoidDate. Thank you to any who
care to help with this.
 
D

Douglas J. Steele

Add a text box to your form, and set its ControlSource property to

=IIf(Nz(Me.txtVoidDate, Date) <= Date, "Membership Expired", "Your
membership will expire in " & DateDiff("d", Date, Nz(Me.txtVoidDate, Date))
& " days.")
 
K

Ken Snell [MVP]

Do you want this message to appear when you open a form? Or when you view a
specific record in a form? Or in some other way?

You can do what you want using an expression similar to this -- if you use
it in the right context, that is:

=IIf(DateDiff("d", Date(), [txtVoidDate]) >= 1 And DateDiff("d", Date(),
[txtVoidDate]) <= 7, "Your membership will expire in " & DateDiff("d",
Date(), [txtVoidDate]) & " day" & IIf(DateDiff("d", Date(), [txtVoidDate]) =
1, "", "s"), IIF(DateDiff("d", Date(), [txtVoidDate]) = 0, "Membership
Expired", ""))
 
K

Keypad

Guess I left one thing out. I want the test to run on the current record, so
I guess the code would need to be in the form's On Current section, right?

I tried putting both versions of code in that section but everything turned
red. I think the code needs to be modified in some way in order to run
correctly in the On Current section.
 
K

Ken Snell [MVP]

The Current event of the form is probably the place to run the desired code.
What we both gave you was not VBA code, but expressions. The VBA code would
look like this:

Private Sub Form_Current()
Dim lngDays As Long
lngDays = DateDiff("d", Date(), Me.txtVoidDate)
Select Case lngDays
Case 1 To 7
MsgBox "Your membership will expire in " & _
lngDays & " day" & _
IIf(lngDays = 1, "", "s"), _
vbOK, "Membership Is Expiring"
Case <= 0
MsgBox "Membership Expired", vbOK, _
"Expired Membership"
End Select
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
K

Keypad

Ken,

I get a "Compile error: Method or data member not found" error message with
this part highlighted in blue

..txtVoidDate

The text box name is correct and the reference name in the code is correct,
so I don't know why it's causing this error.
 
K

Keypad

Ken,

I noticed after I pasted your code in that Access changed Date() to just
Date, could that be a problem?
 
K

Ken Snell [MVP]

The error indicates that ACCESS does not find a textbox control named
"txtVoidDate" on your form. In Design view, check the Name property of that
textbox and see what is there. By chance, are we discussing a main form /
subform setup, where the textbox is in the main form, and the code is
running in the subform?

ACCESS changing Date() to Date in the code is ok.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
K

Keypad

Ken,

There is no subform, the control sits on the main form and that's why it's
odd that Access can't find it. I'm gonna create a new text box control with
a different name and plug your code into that and see what happens. I have
to run an errand right now but will write back with results. Thanks a lot
for hanging in there with me.
 
J

John W. Vinson

Ken,

I get a "Compile error: Method or data member not found" error message with
this part highlighted in blue

.txtVoidDate

The text box name is correct and the reference name in the code is correct,
so I don't know why it's causing this error.
Click to expand...

Please copy and paste your actual code. Do you in fact have

Me.txtVoidDate

on that line as Ken suggested? Or do you maybe have a blank in between?

The Date() changing to Date is normal and nothing to worry about.
 
K

Keypad

Hi John

I created a new text box named txtVoid and plugged Ken's code in it and it
works except with one slight problem. I want the code to start the count at
7 days before the end of the following year. So, I put a date of 5/17/2008
in the text box and got "Membership Expired" when I should get "You have 7
days before membership expires".

Then I changed the text box date to (5/31/2008) and got "Your membership
will expire in 6 days". Somehow the count seems to be off cue. Thanks John.



Here's the code:

Private Sub Form_Current()

Dim lngDays As Long
lngDays = DateDiff("d", Date, Me.txtVoid)
Select Case lngDays
Case 1 To 7
MsgBox "Your membership will expire in " & _
lngDays & " day" & _
IIf(lngDays = 1, "", "s"), _
vbOK, "Membership Is Expiring"
Case Is <= 0
MsgBox "Membership Expired", vbOK, _
"Expired Membership"
End Select
End Sub
 
K

Ken Snell [MVP]

Assuming that you meant to type 2009 instead of 2008 as the dates, the code
is doing what I understood you wanted it to do. A date of 5/1/2009 is before
today's date, so the date is "expired". A date of 5/31/2009 is six days from
today, so there are 6 days between 5/25 and 5/31.

So, to help us identify how to change the code, show us examples of a date
and the message that you expect.
 
K

Keypad

Ken,

So my thinking was if a one year membership was made today on 5/25/09 then
it would expire on 5/25/2010 right. So then the count would begin on
5/18/2010 which would be 7 days before the membership expires. I'm thinking
in terms of there being 365 days in a year.

So, as a test I changed my system date to 5/25/2010 and plugged 5/18/2009
into my text control named txtVoid. Then I put a (Debug.Print lngDays) line
in the section where the code executes and it spit out -365. Is that
correct? Shouldn't it be more like 358. What am I missing?

Here's the code I'm using:

Private Sub Form_Current()

Dim lngDays As Long
lngDays = DateDiff("d", Date, Me.txtVoid)

Debug.Print lngDays

Select Case lngDays
Case 1 To 7
MsgBox "Your membership will expire in " & _
lngDays & " day" & _
IIf(lngDays = 1, "", "s"), _
vbOK, "Membership Is Expiring"

Case Is <= 0
MsgBox "Membership Expired", vbOK, _
"Expired Membership"
End Select

End Sub
 
K

Ken Snell [MVP]

OK, if I'm understanding correctly, try this code:

Private Sub Form_Current()

Dim lngDays As Long
lngDays = Abs(DateDiff("d", DateAdd("yyyy", 1, Me.txtVoid), Date))

Debug.Print lngDays

Select Case lngDays
Case 1 To 7
MsgBox "Your membership will expire in " & _
lngDays & " day" & _
IIf(lngDays = 1, "", "s"), _
vbOK, "Membership Is Expiring"

Case Is <= 0
MsgBox "Membership Expired", vbOK, _
"Expired Membership"
End Select

End Sub


You may find some use from a function that I've written that returns the
next date for an annual, recurring date:
AdjustDateToOneYearWindow
http://www.accessmvp.com/KDSnell/VBA_Functions.htm#AdjDateWindow

If you put the above function in a regular module, then the code could be
changed to this:

Private Sub Form_Current()

Dim lngDays As Long
lngDays = Abs(DateDiff("d", AdjustDateToOneYearWindow(Me.txtVoid), Date))

Debug.Print lngDays

Select Case lngDays
Case 1 To 7
MsgBox "Your membership will expire in " & _
lngDays & " day" & _
IIf(lngDays = 1, "", "s"), _
vbOK, "Membership Is Expiring"

Case Is <= 0
MsgBox "Membership Expired", vbOK, _
"Expired Membership"
End Select

End Sub


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Keypad said:
Ken,

So my thinking was if a one year membership was made today on 5/25/09 then
it would expire on 5/25/2010 right. So then the count would begin on
5/18/2010 which would be 7 days before the membership expires. I'm
thinking
in terms of there being 365 days in a year.

So, as a test I changed my system date to 5/25/2010 and plugged 5/18/2009
into my text control named txtVoid. Then I put a (Debug.Print lngDays)
line
in the section where the code executes and it spit out -365. Is that
correct? Shouldn't it be more like 358. What am I missing?

Here's the code I'm using:

Private Sub Form_Current()

Dim lngDays As Long
lngDays = DateDiff("d", Date, Me.txtVoid)

Debug.Print lngDays

Select Case lngDays
Case 1 To 7
MsgBox "Your membership will expire in " & _
lngDays & " day" & _
IIf(lngDays = 1, "", "s"), _
vbOK, "Membership Is Expiring"

Case Is <= 0
MsgBox "Membership Expired", vbOK, _
"Expired Membership"
End Select

End Sub
Click to expand...
 
K

Keypad

Ken,

I'm on my knees bowing dude. You did it. It works just the way I was
hoping for. There's no way I would have been able to figure this out myself.
Dude, I owe you big time! Thanks a million my friend. Now, on the subject
of some other code I need. Just kidding :) U DA MAN! HOORAY!!!

BTW, I'm sure the other greats who responded to this post could have figured
this one out too and thank you all from the heart.

-KP
 
K

Keypad

Ken,

Man I really hate to say this but I created more records with different
dates in them and the count is getting out of sync. I tried resetting
lngDays to zero at the end of each count but it doesn't help. Any
suggestions?

-KP
 
K

Keypad

Ken,

I have an idea but not sure if it will work. My thinking is we're dealing
with two string values such as 5/25/2009 and 5/25/2010. If there was a way
to subtract the difference between two strings like you would with two
integers, couldn't you arrive at the same result. It makes sense to me and
might be more reliable. Just a thought!

-KP
 
J

John W. Vinson

Ken,

I have an idea but not sure if it will work. My thinking is we're dealing
with two string values such as 5/25/2009 and 5/25/2010. If there was a way
to subtract the difference between two strings like you would with two
integers, couldn't you arrive at the same result. It makes sense to me and
might be more reliable. Just a thought!
Click to expand...

Dates ARE NOT STRINGS.
You cannot do arithmatic with strings. you *can* do date calculations with
Date/Time values, using the functions Ken was suggesting.

Do take a look at the online help for DateDiff, DateAdd, and for dates in
general.
 
D

Douglas J. Steele

John W. Vinson said:
Dates ARE NOT STRINGS.
You cannot do arithmatic with strings. you *can* do date calculations with
Date/Time values, using the functions Ken was suggesting.

Do take a look at the online help for DateDiff, DateAdd, and for dates in
general.
Click to expand...

If you cannot (or will not) convert your fields from text to date, see the
CDate and/or DateValue functions as well.
 

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

Another Date ? Continued 1
Hard Code date 5
Renewing member database design question. 7
Formula using DATES 4
Changing Field in One Table based on Date in Another 2
Date Brackets as an IIf statement 3
constructing database question 4
Most recent date prior to test date. 1

Top