DateDiff Question

[Keypad]

Hello,

OK, I need a way to return only negative values from a DateDiff function.
Everything works correctly, but I want to show only records whose membership
has expired (represented by negative values) then use it in a report.

This would be easy except the report gets sent in an email and the code for
that looks something like:

DoCmd.SendObject objecttype:=acSendReport, _
ObjectName:=strDocName, outputformat:=acFormatHTML, _
To:=strEmail, Subject:=strMailSubject, MessageText:=strMsg


So I was thinking in order to return negative values I would need to do that
in the Report itself, or maybe in the query the report uses. Currently, I
use a function in my query that returns the difference between two dates
which looks like this:

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

Then, in my report I have a text control and in the Control Source I tried
something like:

=([DaysRemaining]) <= 0

but it only returned zero's and minus one. So now I'm wondering if what I'm
trying to do is even possible in this situation.

Can someone point me in the right direction. Thanks in advance.

KP

 

[Marshall Barton]

OK, I need a way to return only negative values from a DateDiff function.
Everything works correctly, but I want to show only records whose membership
has expired (represented by negative values) then use it in a report.

This would be easy except the report gets sent in an email and the code for
that looks something like:

DoCmd.SendObject objecttype:=acSendReport, _
ObjectName:=strDocName, outputformat:=acFormatHTML, _
To:=strEmail, Subject:=strMailSubject, MessageText:=strMsg


So I was thinking in order to return negative values I would need to do that
in the Report itself, or maybe in the query the report uses. Currently, I
use a function in my query that returns the difference between two dates
which looks like this:

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

Then, in my report I have a text control and in the Control Source I tried
something like:

=([DaysRemaining]) <= 0

but it only returned zero's and minus one.

The 0 is Access's value for False and -1 for True so
everything you have seems to be working. The text box in
the report is only good for checking the query field, you
can remove it.

I think all you need to do is use the criteria <=0 in the
DaysRemaining query field's criteria to see only the expired
members in the report.

 

[Keypad]

Marshall,

That did the trick. You are a freakin' genius man. Is it possible to make a
clone of you to address all my Access issues Your awesome dude, thanks a
lot.

KP

OK, I need a way to return only negative values from a DateDiff function.
Everything works correctly, but I want to show only records whose membership
has expired (represented by negative values) then use it in a report.

This would be easy except the report gets sent in an email and the code for
that looks something like:

DoCmd.SendObject objecttype:=acSendReport, _
ObjectName:=strDocName, outputformat:=acFormatHTML, _
To:=strEmail, Subject:=strMailSubject, MessageText:=strMsg


So I was thinking in order to return negative values I would need to do that
in the Report itself, or maybe in the query the report uses. Currently, I
use a function in my query that returns the difference between two dates
which looks like this:

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

Then, in my report I have a text control and in the Control Source I tried
something like:

=([DaysRemaining]) <= 0

but it only returned zero's and minus one.

The 0 is Access's value for False and -1 for True so
everything you have seems to be working. The text box in
the report is only good for checking the query field, you
can remove it.

I think all you need to do is use the criteria <=0 in the
DaysRemaining query field's criteria to see only the expired
members in the report.

 

[Marshall Barton]

Marshall,

That did the trick. You are a freakin' genius man. Is it possible to make a
clone of you to address all my Access issues Your awesome dude, thanks a
lot.

You're welcome.

I don't think my wife could deal with two of me so I won't
even look into cloning possibilities ;-)

Besides, there are lots of folks here that can help with
most any Access ussue. I'd be willing to bet that,
collectively, the groups can help with every Access issue.