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

Keypad wrote:
> 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.

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.

--
_________

Sean Bailey


"Keypad" wrote:

> 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

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

"Beetle" wrote:

> 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.
>
> --
> _________
>
> Sean Bailey
>
>
> "Keypad" wrote:
>
> > 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