Need help with date range parameters

[rorymo]

Hi,
I have a report that contains date parameters:
"begin date" and "end date"
The user can choose the range that he wants the report to use.
I am trying to limit the number of days that the user
can choose because this is a very large report and they
always choose to run it for at least 6 months at a time.
I am having to do this because they won't run it in one or
two month increments.
Anyway, I need to limit the search date range to 3 months.
If the range that the user puts in is over 3 months, then
I want to only return records from the beginning date that
he chose 3 months forward, otherwise just use the dates
that he chose.
This is the formula I'm trying to use:
if DateDiff("d",{?End Date},{?Begin Date}) > 95
then
DateAdd("d",95,{?Begin Date})
else
use the original choices of {?Begin Date},{?End Date}

I'm not sure the syntax to put after the "else" clause.

Also, I don't have any idea where to put this formula.
I tried it in the record selection but I already have a
lot going on in that and it didn't like the syntax.

Any help would be appreciated!
Thanks,
Rory

 

[Turkbear]

Hi,
It can be in the Record selection.

Try adding the following to your existing criteria

AND
(
if DateDiff("d",{?End Date},{?Begin Date}) > 95
then
{DATABASETABLE.DATEFIELD} in {?Begin Date} to {?DateAdd("d",95,{?Begin Date})
else
{DATABASETABLE.DATEFIELD} in {?Begin Date} to {?End Date}
)

You would substitute your actual field name for the date to be tested, of course.

hth,

 

[rhinok]

Try using this in your record selection criteria:

{table.datefield} In
IIF(DateDiff("d",{?Begin Date},{?End Date}) > 95, {?Begin Date} To DateAdd("d",95,{?Begin Date}),{?Begin Date} To {?End Date})

Please note, in order for this to be passed to the database for processing, you must have 'Convert Date-Time Field' set to "Date-Time" your Report Options. The DateDiff and DateAdd functions won't be passed otherwise.

Also, if you're going to govern the date range processed by the report, then you should create a text-based formula that indicates the date range selected was greater than the allowed range and that the records returned were between {?Begin Date} and DateAdd("d",95,{?Begin Date}). This will mitigate risk of users publishing reports with incorrect data (based on entered ranges that didn't return data) and users calling you because the report is "wrong".


~Kurt

 

[rorymo]

Thank you both for your answers!
I'll try them right away.
Rory

 

[rhinok]

Good luck rorymo,

Since you're adding this to existing selection criteria don't forget to put an 'And' before the code (unless its the first statement). Also, the date parameters were backwards in your DateDiff formula - I swapped them in my code.


~Kurt

 

[rorymo]

Turkbear,
I tried the code, but I am not getting the
results I expected.
When you say "{DATABASETABLE.DATEFIELD}" I am not sure
what you are referring to.
The table has more than one date field.
There is an "Open Date" date field and a "Close Date" date
field, but these refer to dates that tickets were created
and closed (this is a helpdesk database).
I used the "Open" date field for the"{DATABASETABLE.DATEFIELD}" ,with a beginning
date of Jan. 1 and ending date of Apr. 15.
The query should have returned tickets that were created
from Jan 1 to March 31, but returned tickets with the the Apr. 15 date.
Thanks,
Rory

 

[rhinok]

In order for the code to filter your records, you need to apply it to an actual database field. The Open Date field is probably the correct field, unless you have a 'create date' field.

Also, Turkbear's code has a typo and doesn't swap the {?Begin Date} and {?End Date} parameters in the DateDiff function like it should. As a test, create a formula containing

DateDiff("d",{?End Date},{?Begin Date})

and place it in your report. If you enter valid Begin and End Dates, the result will be negative. Next, swap the parameter fields and you'll get the right results.

For the complete filter,try:

AND
(
if DateDiff("d",{?Begin Date},{?End Date}) > 95
then
{DATABASETABLE.DATEFIELD} in {?Begin Date} to DateAdd("d",95,{?Begin Date})
else
{DATABASETABLE.DATEFIELD} in {?Begin Date} to {?End Date}
)

Keep in mind that this code will not be passed to the database for processing. That means that 10 years of records could be returned and would then be filtered out client side, which really defeats the purpose of governing the date range. All you'd be doing is hiding records when they've already been returned to the client and you've taken a big hit on the db.


~Kurt

 

[rorymo]

Thanks Rhinok,
it worked great!
Rory