Beetle - I Need You Again

[Keypad]

Beetle,

I got a new set of dates to handle with 2 digit years. Based on current
date, the DateDiff string you gave me doesn't seem to handle the dates I have
below. Everything comes up with minus, I think because all of them are past
the current date. How do I handle these kinds of dates?

04/04/10
11/19/09
01/21/10
12/17/09
02/22/10
09/10/09
11/30/09

 

[Beetle]

Where are these date values coming from? Are they formatted values
from an actual Date/Time field, or are they just string values from a Text
field?

If the latter, the DateDiff function won't be able to interpret them correctly
unless the are surrounded with the proper date delimiters, like;

DateDiff("d", Date(), #04/04/10#)

 

[Beetle]

It's also possible that you need to switch the position of
the two date values within your DateDiff function if it is
returning negative numbers.

 

[Keypad]

Beetle,

I changed the DateDiff string a little and looks like I'm getting the
correct values now. The string I changed it too is:

DaysRemaining: DateDiff("d",Date(),[Void_Date])

These are the values I get, so let me know if they look correct to you:

Date: Days Remaining
01/19/10 228
11/19/09 167
01/21/10 230
05/05/10 334
11/13/09 161

Beetle, thanks again for all your help. Your the best

KP

 

[Keypad]

Beetle,

Forgot to mention the dates I submitted earlier are string values in a text
box. Can you explain again how DateDiff is supposed to work. Maybe I'm not
interpreting things correctly. I still don't know if what I'm getting is
correct!

KP

 

[Keypad]

Beetle,

I learned something interesting about the DateDiff function on MSDN in the
Visual Basic 6.0 Resource Center under:

MSDN Library/Development Tools and Languages/Visual Studio 6.0/Visual Basic
6.0/Product Documentation/Reference/Language Reference/Functions/D/DateDiff
Function

Here's part of what it say's:

(If date1 refers to a later point in time than date2, the DateDiff function
returns a negative number.

If date1 or date2 is a date literal, the specified year becomes a permanent
part of that date. However, if date1 or date2 is enclosed in double quotation
marks (" "), and you omit the year, the current year is inserted in your code
each time the date1 or date2 expression is evaluated. This makes it possible
to write code that can be used in different years.)

So, based on the explanation above the first part would explain why I get
the negative numbers. The second part looks like what to do when dealing
with expiration dates of different years. Trouble is, does anyone know how
to code something like what's being described in the second part. I never
figured this would be so complicated when I intially took on this project. I
feel like I'm in deep, deep s*** now. There must be some way to solve this.
I'm sure I'm not the first to ever face this challenge. The answers out
there as they say in X Files, the bigger question is WHERE!

Thanks for listening Beetle

KP

 

[John W. Vinson]

If date1 or date2 is a date literal, the specified year becomes a permanent
part of that date. However, if date1 or date2 is enclosed in double quotation
marks (" "), and you omit the year, the current year is inserted in your code
each time the date1 or date2 expression is evaluated. This makes it possible
to write code that can be used in different years.)

So, based on the explanation above the first part would explain why I get
the negative numbers. The second part looks like what to do when dealing
with expiration dates of different years. Trouble is, does anyone know how
to code something like what's being described in the second part.

Ummmmm...

DateDiff("d", "6/4", [datefield])

will get the number of days between 6/4/2009 until the value in datefield, if
it's run during 2009. If it's run in 2010, it will get the days between
6/4/2010 and the value in datefield.

Is that what you mean by "the second part"???

 

[Keypad]

John,

That's a good question. Guess I would have to test it out and see what the
results look like. First off though, were I to use the approach you
submitted as an interpretation of what MSDN say's about DateDiff I foresee
the need to have to use other functions to slice and dice the dates to get
the ##/## or #/# part of the date before I could have anything useful. The
second task would be how to use it in a query.

Honestly John, this stuff is way over my head. I have switched dates around,
used ABS(), everything I can think of and stuff others have suggested. Just
when I think I have it all solved a new problem creeps into the picture. You
folks are fantastic though because you don't give up. That's why I haven't
thrown in the towel myself. I'll keep at it John thanks to you and all the
others who have contributed.

KP

If date1 or date2 is a date literal, the specified year becomes a permanent
part of that date. However, if date1 or date2 is enclosed in double quotation
marks (" "), and you omit the year, the current year is inserted in your code
each time the date1 or date2 expression is evaluated. This makes it possible
to write code that can be used in different years.)

So, based on the explanation above the first part would explain why I get
the negative numbers. The second part looks like what to do when dealing
with expiration dates of different years. Trouble is, does anyone know how
to code something like what's being described in the second part.

Ummmmm...

DateDiff("d", "6/4", [datefield])

will get the number of days between 6/4/2009 until the value in datefield, if
it's run during 2009. If it's run in 2010, it will get the days between
6/4/2010 and the value in datefield.

Is that what you mean by "the second part"???

 

[John W. Vinson]

John,

That's a good question. Guess I would have to test it out and see what the
results look like. First off though, were I to use the approach you
submitted as an interpretation of what MSDN say's about DateDiff I foresee
the need to have to use other functions to slice and dice the dates to get
the ##/## or #/# part of the date before I could have anything useful. The
second task would be how to use it in a query.

Seriously:

You are making this a LOT HARDER than it actually is.

If you have a due date and you want to see how many days it is in the future,
you don't need to slice and dice ANYTHING.

Maybe take a day off, get some rest, and come back and reread this thread. It
is *simpler than you are making it*.

 

[Keypad]

John,

You are so right. I need to step away from this for a day or so, clear my
head. Lots of turmoil in my life these days which explains why I'm awake
again in the middle of the night. Thanks my friend!

KP

:

 

[Keypad]

John, Beetle,

After looking at my scenario further I have concluded that a good approach
might be to use two different Select Case statements. The first Case would
handle all dates in the current year 2009 where DateDiff returns positive
numbers. The second case would handle all dates beyond current year
(2010...) where DateDiff returns negative numbers. With this principle in
mind, the Case tests might look something like the following:


If Me.txtDaysRemaining >= 0 then
Select Case Me.txtDaysRemaining
Case 0 To 7
Me.lblVoid.Caption = "Membership Will Expire In " _
& Me.txtDaysRemaining & " Day(s)"

Case Is = 0
Call MembershipExpired
End Select

Elseif Me.txtDaysRemaining <= 0 then
Select Case Me.txtDaysRemaining
Case -7 To 0
Me.lblVoid.Caption = "Membership Will Expire In " _
& ABS(Me.txtDaysRemaining) & " Day(s)"

Case Is = 0
Call MembershipExpired
End Select
End If


So if the above scenario worked, then I would only need to figure out how to
deal with memberships once they go beyond the expiration date. Can't figure
out how to do that though without having a special field in my table that
marks the expired membership. I know Beetle would not agree to having such a
field in my table but I see no other way around it since I rely on
DaysRemaining being zero, and staying zero when a membership expires.


-KP

 

[Beetle]

The second case would handle all dates beyond current year

(2010...) where DateDiff returns negative numbers.

I don't know what you are doing, but the only way that would
happen is if you put the later (2010) date first. This;

DateDiff("d", #6/8/2009#, #6/8/2010#)

will return 365

This;

DateDiff("d", #6/8/2010#, #6/8/2009#)

will return -365

DateDiff is a VERY SIMPLE function, so I don't know why you are
having so much trouble with it.

Why don't you post your table structure (ie - the table where the
membership start date field exists), and maybe we can go form there.
The only value you should need to store is the membership
start / renewal date. Everything beyond that is a simple calculation.

 

[Keypad]

Beetle,

The function I am using in my query is:

DaysRemaining: (DateDiff("d",[Void_Date],Date()))

Here's my table (Main) structure:

MainID = PK
Badge_ID = FK
DaysHours_ID = FK
Security_ID = FK
Contact_ID = FK
Company_ID = FK
Full_Name = Text
Company_Name = Text
Contact_Name = Text
Days_Hours = Text
Start_Date = Date/Time
Void_Date = Date/Time
Badge_Type = Text

Thanks for hanging in there with me Beetle.

KP

 

[Beetle]

Well, to address the specific issue you posted about, I'm going to assume
that Void_Date
represents the membership expiration date. If that is the case, then you
have the date
values in the expression backwards. It should be;

DaysRemaining: DateDiff("d", Date(), [Void_Date])

However, there are other issues that you should consider, or at least be
aware of.

First, if all memberships expire 1 year from the Start_Date, then you
shouldn't have
the Void_Date field in your table at all. It should be a calculated field in
a query as
I explained in a previous thread.

Second, it would appear you have several other unnecessary fields in your
table as well.
You have the following fields as Foreign Keys;

Badge_ID = FK
DaysHours_ID = FK
Security_ID = FK
Contact_ID = FK
Company_ID = FK

which means that, presumably, you have tables for each of those entities.
So, the
following fields should already exist in those tables;

Company_Name = Text
Contact_Name = Text
Days_Hours = Text
Badge_Type = Text

You don't need to (and shouldn't) redundantly store these values in your
Main table. You
simply retrieve the values from their respective tables based on the FK
value that exists
in the Main table. You do this through the use of queries or calculated
controls on forms
or reports.

Additionally, if the Full_Name field represents a persons name, it should be
separated into
FirstName and LastName, not stored in one field.

_________

Sean Bailey

Beetle,

The function I am using in my query is:

DaysRemaining: (DateDiff("d",[Void_Date],Date()))

Here's my table (Main) structure:

MainID = PK
Badge_ID = FK
DaysHours_ID = FK
Security_ID = FK
Contact_ID = FK
Company_ID = FK
Full_Name = Text
Company_Name = Text
Contact_Name = Text
Days_Hours = Text
Start_Date = Date/Time
Void_Date = Date/Time
Badge_Type = Text

Thanks for hanging in there with me Beetle.

KP

I don't know what you are doing, but the only way that would
happen is if you put the later (2010) date first. This;

DateDiff("d", #6/8/2009#, #6/8/2010#)

will return 365

This;

DateDiff("d", #6/8/2010#, #6/8/2009#)

will return -365

DateDiff is a VERY SIMPLE function, so I don't know why you are
having so much trouble with it.

Why don't you post your table structure (ie - the table where the
membership start date field exists), and maybe we can go form there.
The only value you should need to store is the membership
start / renewal date. Everything beyond that is a simple calculation.

 

[Keypad]

Beetle,

Thanks for looking things over for me.

First, if all memberships expire 1 year from the Start_Date, then you
shouldn't have
the Void_Date field in your table at all. It should be a calculated field in
a query

I wish memberships were that simple. Most are one year, but others are like
5 or 6 months. Worse still, for reasons I cannot explain in this forum I
have two different sets of documents that represent two types of memberships.
One membership type has a Start Date and End Date, the other only has an End
Date. I asked about that and what I got back was sort of don't ask, don't
tell. I'm literally trying to put a database together with what I have on
paper.

You don't need to (and shouldn't) redundantly store these values in your
Main table. You
simply retrieve the values from their respective tables based on the FK
value that exists
in the Main table. You do this through the use of queries or calculated
controls on forms
or reports.

Yea, I was looking at them that way too. Thanks for showing me how to deal
with them. If I get this thing to work correctly, it will only be by the
grace of God along with the help I get in these forums. The folks who asked
for this are brutal. I want my mommy :-(

At first glance this looked like a pretty simple database. What the hell
was I thinking. At worse I'll just end up in the Hudson River bound and
gagged

KP

Well, to address the specific issue you posted about, I'm going to assume
that Void_Date
represents the membership expiration date. If that is the case, then you
have the date
values in the expression backwards. It should be;

DaysRemaining: DateDiff("d", Date(), [Void_Date])

However, there are other issues that you should consider, or at least be
aware of.

First, if all memberships expire 1 year from the Start_Date, then you
shouldn't have
the Void_Date field in your table at all. It should be a calculated field in
a query as
I explained in a previous thread.

Second, it would appear you have several other unnecessary fields in your
table as well.
You have the following fields as Foreign Keys;

Badge_ID = FK
DaysHours_ID = FK
Security_ID = FK
Contact_ID = FK
Company_ID = FK

which means that, presumably, you have tables for each of those entities.
So, the
following fields should already exist in those tables;

Company_Name = Text
Contact_Name = Text
Days_Hours = Text
Badge_Type = Text

You don't need to (and shouldn't) redundantly store these values in your
Main table. You
simply retrieve the values from their respective tables based on the FK
value that exists
in the Main table. You do this through the use of queries or calculated
controls on forms
or reports.

Additionally, if the Full_Name field represents a persons name, it should be
separated into
FirstName and LastName, not stored in one field.

_________

Sean Bailey

Beetle,

The function I am using in my query is:

DaysRemaining: (DateDiff("d",[Void_Date],Date()))

Here's my table (Main) structure:

MainID = PK
Badge_ID = FK
DaysHours_ID = FK
Security_ID = FK
Contact_ID = FK
Company_ID = FK
Full_Name = Text
Company_Name = Text
Contact_Name = Text
Days_Hours = Text
Start_Date = Date/Time
Void_Date = Date/Time
Badge_Type = Text

Thanks for hanging in there with me Beetle.

KP

The second case would handle all dates beyond current year
(2010...) where DateDiff returns negative numbers.

I don't know what you are doing, but the only way that would
happen is if you put the later (2010) date first. This;

DateDiff("d", #6/8/2009#, #6/8/2010#)

will return 365

This;

DateDiff("d", #6/8/2010#, #6/8/2009#)

will return -365

DateDiff is a VERY SIMPLE function, so I don't know why you are
having so much trouble with it.

Why don't you post your table structure (ie - the table where the
membership start date field exists), and maybe we can go form there.
The only value you should need to store is the membership
start / renewal date. Everything beyond that is a simple calculation.

--
_________

Sean Bailey


:

John, Beetle,

After looking at my scenario further I have concluded that a good
approach
might be to use two different Select Case statements. The first Case
would
handle all dates in the current year 2009 where DateDiff returns
positive
numbers. The second case would handle all dates beyond current year
(2010...) where DateDiff returns negative numbers. With this principle
in
mind, the Case tests might look something like the following:


If Me.txtDaysRemaining >= 0 then
Select Case Me.txtDaysRemaining
Case 0 To 7
Me.lblVoid.Caption = "Membership Will Expire In " _
& Me.txtDaysRemaining & " Day(s)"

Case Is = 0
Call MembershipExpired
End Select

Elseif Me.txtDaysRemaining <= 0 then
Select Case Me.txtDaysRemaining
Case -7 To 0
Me.lblVoid.Caption = "Membership Will Expire In " _
& ABS(Me.txtDaysRemaining) & " Day(s)"

Case Is = 0
Call MembershipExpired
End Select
End If


So if the above scenario worked, then I would only need to figure out
how to
deal with memberships once they go beyond the expiration date. Can't
figure
out how to do that though without having a special field in my table
that
marks the expired membership. I know Beetle would not agree to having
such a
field in my table but I see no other way around it since I rely on
DaysRemaining being zero, and staying zero when a membership expires.


-KP

:

On Fri, 5 Jun 2009 18:56:01 -0700, Keypad
<[email protected]>
wrote:

John,

That's a good question. Guess I would have to test it out and see
what the
results look like. First off though, were I to use the approach you
submitted as an interpretation of what MSDN say's about DateDiff I
foresee
the need to have to use other functions to slice and dice the dates
to get
the ##/## or #/# part of the date before I could have anything
useful. The
second task would be how to use it in a query.

Seriously:

You are making this a LOT HARDER than it actually is.

If you have a due date and you want to see how many days it is in the
future,
you don't need to slice and dice ANYTHING.

Maybe take a day off, get some rest, and come back and reread this
thread. It
is *simpler than you are making it*.

 

[Keypad]

Beetle,

I hope you are still reading this post. I switched things around like you
said and it's working except for one thing. I was testing things by changing
the current date to force my MembershipExpired function to kick in. The
function fires when it's supposed to and I get negative numbers from DateDiff
when the Void Date expires which is what I want. Problem now is, when I
change the current date back to where it should be then DateDiff doesn't
catch up right away. DateDiff gives me back the same negative numbers until
I move forward and backward between records, or close then reopen the form.

Have you had any such experience with DateDiff?

KP

Well, to address the specific issue you posted about, I'm going to assume
that Void_Date
represents the membership expiration date. If that is the case, then you
have the date
values in the expression backwards. It should be;

DaysRemaining: DateDiff("d", Date(), [Void_Date])

However, there are other issues that you should consider, or at least be
aware of.

First, if all memberships expire 1 year from the Start_Date, then you
shouldn't have
the Void_Date field in your table at all. It should be a calculated field in
a query as
I explained in a previous thread.

Second, it would appear you have several other unnecessary fields in your
table as well.
You have the following fields as Foreign Keys;

Badge_ID = FK
DaysHours_ID = FK
Security_ID = FK
Contact_ID = FK
Company_ID = FK

which means that, presumably, you have tables for each of those entities.
So, the
following fields should already exist in those tables;

Company_Name = Text
Contact_Name = Text
Days_Hours = Text
Badge_Type = Text

You don't need to (and shouldn't) redundantly store these values in your
Main table. You
simply retrieve the values from their respective tables based on the FK
value that exists
in the Main table. You do this through the use of queries or calculated
controls on forms
or reports.

Additionally, if the Full_Name field represents a persons name, it should be
separated into
FirstName and LastName, not stored in one field.

_________

Sean Bailey

Beetle,

The function I am using in my query is:

DaysRemaining: (DateDiff("d",[Void_Date],Date()))

Here's my table (Main) structure:

MainID = PK
Badge_ID = FK
DaysHours_ID = FK
Security_ID = FK
Contact_ID = FK
Company_ID = FK
Full_Name = Text
Company_Name = Text
Contact_Name = Text
Days_Hours = Text
Start_Date = Date/Time
Void_Date = Date/Time
Badge_Type = Text

Thanks for hanging in there with me Beetle.

KP

The second case would handle all dates beyond current year
(2010...) where DateDiff returns negative numbers.

I don't know what you are doing, but the only way that would
happen is if you put the later (2010) date first. This;

DateDiff("d", #6/8/2009#, #6/8/2010#)

will return 365

This;

DateDiff("d", #6/8/2010#, #6/8/2009#)

will return -365

DateDiff is a VERY SIMPLE function, so I don't know why you are
having so much trouble with it.

Why don't you post your table structure (ie - the table where the
membership start date field exists), and maybe we can go form there.
The only value you should need to store is the membership
start / renewal date. Everything beyond that is a simple calculation.

--
_________

Sean Bailey


:

John, Beetle,

After looking at my scenario further I have concluded that a good
approach
might be to use two different Select Case statements. The first Case
would
handle all dates in the current year 2009 where DateDiff returns
positive
numbers. The second case would handle all dates beyond current year
(2010...) where DateDiff returns negative numbers. With this principle
in
mind, the Case tests might look something like the following:


If Me.txtDaysRemaining >= 0 then
Select Case Me.txtDaysRemaining
Case 0 To 7
Me.lblVoid.Caption = "Membership Will Expire In " _
& Me.txtDaysRemaining & " Day(s)"

Case Is = 0
Call MembershipExpired
End Select

Elseif Me.txtDaysRemaining <= 0 then
Select Case Me.txtDaysRemaining
Case -7 To 0
Me.lblVoid.Caption = "Membership Will Expire In " _
& ABS(Me.txtDaysRemaining) & " Day(s)"

Case Is = 0
Call MembershipExpired
End Select
End If


So if the above scenario worked, then I would only need to figure out
how to
deal with memberships once they go beyond the expiration date. Can't
figure
out how to do that though without having a special field in my table
that
marks the expired membership. I know Beetle would not agree to having
such a
field in my table but I see no other way around it since I rely on
DaysRemaining being zero, and staying zero when a membership expires.


-KP

:

On Fri, 5 Jun 2009 18:56:01 -0700, Keypad
<[email protected]>
wrote:

John,

That's a good question. Guess I would have to test it out and see
what the
results look like. First off though, were I to use the approach you
submitted as an interpretation of what MSDN say's about DateDiff I
foresee
the need to have to use other functions to slice and dice the dates
to get
the ##/## or #/# part of the date before I could have anything
useful. The
second task would be how to use it in a query.

Seriously:

You are making this a LOT HARDER than it actually is.

If you have a due date and you want to see how many days it is in the
future,
you don't need to slice and dice ANYTHING.

Maybe take a day off, get some rest, and come back and reread this
thread. It
is *simpler than you are making it*.

 

[Beetle]

A calculated field or an expression will be re-evaluated automatically
when there is a change within the Access application (i.e. - you update
one of the fields that the calculation is based on, you move to a different
record, you close and open the form, etc.). Changing your Windows
system date happens outside of the Access application, so there is no reason
why this would automatically trigger anything to happen within Access.

I don't see why it matters unless you regularly modify your system date,
and I can't see why you would be doing that.

_________

Sean Bailey

Beetle,

I hope you are still reading this post. I switched things around like you
said and it's working except for one thing. I was testing things by
changing
the current date to force my MembershipExpired function to kick in. The
function fires when it's supposed to and I get negative numbers from
DateDiff
when the Void Date expires which is what I want. Problem now is, when I
change the current date back to where it should be then DateDiff doesn't
catch up right away. DateDiff gives me back the same negative numbers
until
I move forward and backward between records, or close then reopen the
form.

Have you had any such experience with DateDiff?

KP

Well, to address the specific issue you posted about, I'm going to assume
that Void_Date
represents the membership expiration date. If that is the case, then you
have the date
values in the expression backwards. It should be;

DaysRemaining: DateDiff("d", Date(), [Void_Date])

However, there are other issues that you should consider, or at least be
aware of.

First, if all memberships expire 1 year from the Start_Date, then you
shouldn't have
the Void_Date field in your table at all. It should be a calculated field
in
a query as
I explained in a previous thread.

Second, it would appear you have several other unnecessary fields in your
table as well.
You have the following fields as Foreign Keys;

Badge_ID = FK
DaysHours_ID = FK
Security_ID = FK
Contact_ID = FK
Company_ID = FK

which means that, presumably, you have tables for each of those entities.
So, the
following fields should already exist in those tables;

Company_Name = Text
Contact_Name = Text
Days_Hours = Text
Badge_Type = Text

You don't need to (and shouldn't) redundantly store these values in your
Main table. You
simply retrieve the values from their respective tables based on the FK
value that exists
in the Main table. You do this through the use of queries or calculated
controls on forms
or reports.

Additionally, if the Full_Name field represents a persons name, it should
be
separated into
FirstName and LastName, not stored in one field.

_________

Sean Bailey

Beetle,

The function I am using in my query is:

DaysRemaining: (DateDiff("d",[Void_Date],Date()))

Here's my table (Main) structure:

MainID = PK
Badge_ID = FK
DaysHours_ID = FK
Security_ID = FK
Contact_ID = FK
Company_ID = FK
Full_Name = Text
Company_Name = Text
Contact_Name = Text
Days_Hours = Text
Start_Date = Date/Time
Void_Date = Date/Time
Badge_Type = Text

Thanks for hanging in there with me Beetle.

KP


:

The second case would handle all dates beyond current year
(2010...) where DateDiff returns negative numbers.

I don't know what you are doing, but the only way that would
happen is if you put the later (2010) date first. This;

DateDiff("d", #6/8/2009#, #6/8/2010#)

will return 365

This;

DateDiff("d", #6/8/2010#, #6/8/2009#)

will return -365

DateDiff is a VERY SIMPLE function, so I don't know why you are
having so much trouble with it.

Why don't you post your table structure (ie - the table where the
membership start date field exists), and maybe we can go form there.
The only value you should need to store is the membership
start / renewal date. Everything beyond that is a simple calculation.

--
_________

Sean Bailey


:

John, Beetle,

After looking at my scenario further I have concluded that a good
approach
might be to use two different Select Case statements. The first
Case
would
handle all dates in the current year 2009 where DateDiff returns
positive
numbers. The second case would handle all dates beyond current year
(2010...) where DateDiff returns negative numbers. With this
principle
in
mind, the Case tests might look something like the following:


If Me.txtDaysRemaining >= 0 then
Select Case Me.txtDaysRemaining
Case 0 To 7
Me.lblVoid.Caption = "Membership Will Expire In " _
& Me.txtDaysRemaining & " Day(s)"

Case Is = 0
Call MembershipExpired
End Select

Elseif Me.txtDaysRemaining <= 0 then
Select Case Me.txtDaysRemaining
Case -7 To 0
Me.lblVoid.Caption = "Membership Will Expire In " _
& ABS(Me.txtDaysRemaining) & " Day(s)"

Case Is = 0
Call MembershipExpired
End Select
End If


So if the above scenario worked, then I would only need to figure
out
how to
deal with memberships once they go beyond the expiration date.
Can't
figure
out how to do that though without having a special field in my table
that
marks the expired membership. I know Beetle would not agree to
having
such a
field in my table but I see no other way around it since I rely on
DaysRemaining being zero, and staying zero when a membership
expires.


-KP

:

On Fri, 5 Jun 2009 18:56:01 -0700, Keypad
<[email protected]>
wrote:

John,

That's a good question. Guess I would have to test it out and
see
what the
results look like. First off though, were I to use the approach
you
submitted as an interpretation of what MSDN say's about DateDiff
I
foresee
the need to have to use other functions to slice and dice the
dates
to get
the ##/## or #/# part of the date before I could have anything
useful. The
second task would be how to use it in a query.

Seriously:

You are making this a LOT HARDER than it actually is.

If you have a due date and you want to see how many days it is in
the
future,
you don't need to slice and dice ANYTHING.

Maybe take a day off, get some rest, and come back and reread this
thread. It
is *simpler than you are making it*.

 

[Keypad]

Beetle,

Wow, you are always right on the money Beetle. I thank you in a major way
for all the help you have given with my tables, DateDiff and everything else.
Thank you for not giving up on me.

Also want to thank John Vinson too for his Access contributions and general
wisdom. I have learned so much from you both

KP

A calculated field or an expression will be re-evaluated automatically
when there is a change within the Access application (i.e. - you update
one of the fields that the calculation is based on, you move to a different
record, you close and open the form, etc.). Changing your Windows
system date happens outside of the Access application, so there is no reason
why this would automatically trigger anything to happen within Access.

I don't see why it matters unless you regularly modify your system date,
and I can't see why you would be doing that.

_________

Sean Bailey

Beetle,

I hope you are still reading this post. I switched things around like you
said and it's working except for one thing. I was testing things by
changing
the current date to force my MembershipExpired function to kick in. The
function fires when it's supposed to and I get negative numbers from
DateDiff
when the Void Date expires which is what I want. Problem now is, when I
change the current date back to where it should be then DateDiff doesn't
catch up right away. DateDiff gives me back the same negative numbers
until
I move forward and backward between records, or close then reopen the
form.

Have you had any such experience with DateDiff?

KP

Well, to address the specific issue you posted about, I'm going to assume
that Void_Date
represents the membership expiration date. If that is the case, then you
have the date
values in the expression backwards. It should be;

DaysRemaining: DateDiff("d", Date(), [Void_Date])

However, there are other issues that you should consider, or at least be
aware of.

First, if all memberships expire 1 year from the Start_Date, then you
shouldn't have
the Void_Date field in your table at all. It should be a calculated field
in
a query as
I explained in a previous thread.

Second, it would appear you have several other unnecessary fields in your
table as well.
You have the following fields as Foreign Keys;

Badge_ID = FK
DaysHours_ID = FK
Security_ID = FK
Contact_ID = FK
Company_ID = FK

which means that, presumably, you have tables for each of those entities.
So, the
following fields should already exist in those tables;

Company_Name = Text
Contact_Name = Text
Days_Hours = Text
Badge_Type = Text

You don't need to (and shouldn't) redundantly store these values in your
Main table. You
simply retrieve the values from their respective tables based on the FK
value that exists
in the Main table. You do this through the use of queries or calculated
controls on forms
or reports.

Additionally, if the Full_Name field represents a persons name, it should
be
separated into
FirstName and LastName, not stored in one field.

_________

Sean Bailey


Beetle,

The function I am using in my query is:

DaysRemaining: (DateDiff("d",[Void_Date],Date()))

Here's my table (Main) structure:

MainID = PK
Badge_ID = FK
DaysHours_ID = FK
Security_ID = FK
Contact_ID = FK
Company_ID = FK
Full_Name = Text
Company_Name = Text
Contact_Name = Text
Days_Hours = Text
Start_Date = Date/Time
Void_Date = Date/Time
Badge_Type = Text

Thanks for hanging in there with me Beetle.

KP


:

The second case would handle all dates beyond current year
(2010...) where DateDiff returns negative numbers.

I don't know what you are doing, but the only way that would
happen is if you put the later (2010) date first. This;

DateDiff("d", #6/8/2009#, #6/8/2010#)

will return 365

This;

DateDiff("d", #6/8/2010#, #6/8/2009#)

will return -365

DateDiff is a VERY SIMPLE function, so I don't know why you are
having so much trouble with it.

Why don't you post your table structure (ie - the table where the
membership start date field exists), and maybe we can go form there.
The only value you should need to store is the membership
start / renewal date. Everything beyond that is a simple calculation.

--
_________

Sean Bailey


:

John, Beetle,

After looking at my scenario further I have concluded that a good
approach
might be to use two different Select Case statements. The first
Case
would
handle all dates in the current year 2009 where DateDiff returns
positive
numbers. The second case would handle all dates beyond current year
(2010...) where DateDiff returns negative numbers. With this
principle
in
mind, the Case tests might look something like the following:


If Me.txtDaysRemaining >= 0 then
Select Case Me.txtDaysRemaining
Case 0 To 7
Me.lblVoid.Caption = "Membership Will Expire In " _
& Me.txtDaysRemaining & " Day(s)"

Case Is = 0
Call MembershipExpired
End Select

Elseif Me.txtDaysRemaining <= 0 then
Select Case Me.txtDaysRemaining
Case -7 To 0
Me.lblVoid.Caption = "Membership Will Expire In " _
& ABS(Me.txtDaysRemaining) & " Day(s)"

Case Is = 0
Call MembershipExpired
End Select
End If


So if the above scenario worked, then I would only need to figure
out
how to
deal with memberships once they go beyond the expiration date.
Can't
figure
out how to do that though without having a special field in my table
that
marks the expired membership. I know Beetle would not agree to
having
such a
field in my table but I see no other way around it since I rely on
DaysRemaining being zero, and staying zero when a membership
expires.


-KP

:

On Fri, 5 Jun 2009 18:56:01 -0700, Keypad
<[email protected]>
wrote:

John,

That's a good question. Guess I would have to test it out and
see
what the
results look like. First off though, were I to use the approach
you
submitted as an interpretation of what MSDN say's about DateDiff
I
foresee
the need to have to use other functions to slice and dice the
dates
to get
the ##/## or #/# part of the date before I could have anything
useful. The
second task would be how to use it in a query.

Seriously:

You are making this a LOT HARDER than it actually is.

If you have a due date and you want to see how many days it is in
the
future,
you don't need to slice and dice ANYTHING.

Maybe take a day off, get some rest, and come back and reread this
thread. It
is *simpler than you are making it*.