Beetle - What Do I Do Now

[Keypad]

Hello,

My previous database was saving the results of DateDiff into a field in my
table. I learned a better approach was to use a query to get the days
remaining between two dates and perform an action on the fly, instead of
saving data to a table. That worked well until I noticed that when DateDiff
counts down to the end of the year, on the following day it begins it's count
again starting at one.

That won't work because in this case I would have act on the day of
expiration otherwise it's a mute point for any membership that goes beyond
expiration.
So, my question is how do you handle this situation without saving data?

KP

 

[Mike Painter]

Hello,

My previous database was saving the results of DateDiff into a field
in my table. I learned a better approach was to use a query to get
the days remaining between two dates and perform an action on the
fly, instead of saving data to a table. That worked well until I
noticed that when DateDiff counts down to the end of the year, on the
following day it begins it's count again starting at one.

That won't work because in this case I would have act on the day of
expiration otherwise it's a mute point for any membership that goes
beyond expiration.
So, my question is how do you handle this situation without saving
data?

KP

DateDiff ( "d", date1, date2) gives the number of days between any two dates
and is not affected by a year boundry.
Are you storing the dates in a date field?
Are you including the year in the date #12/31/09#?
If not Access assume it is the current year.

 

[Beetle]

Can you post the expression you are using now?

I noticed in your original post you were wrapping the DateDiff
in the Abs function. I don't know why you were doing this, but if
you're still doing it, that would cause the behavior you are seeing
because once Date1 becomes greater than Date2 the returned
result will be a negative number, but the Abs function will convert
it to it's absolute value, so -1 will become 1, etc.

 

[Keypad]

Beetle,

You are right once again. I was wrapping DateDiff in Abs() but once I
removed Abs then my expired memberships remained that way until the void
dates were changed. I haven't checked things out completely but so far so
good, looks like everything is working. Thanks again for your patience and
persistance. YOU ROCK!!!

KP