Using formula in selection parameters

[Former Member]

Hi,

I have a table (APPROPRIATION_TRACKING) that shows payment history. It has separate fields for payment month, day and year, and a field showing the amount paid.

I have created a formula that converts the separate month/day/year fields into a single date:

totext({APPROPRIATION_TRACKING.APPR_MM},"0#") & "-" & totext({APPROPRIATION_TRACKING.APPR_DD},"0#") & "-" & totext({APPROPRIATION_TRACKING.APPR_CCYY},"####")

I want the user to be able to select a date range, and then get payments made in that date range.

But when I am creating the parameter, I don't see the formula in the "Value Field" selection box.

I know this is possible but can't figure it out...any ideas?

thx jon

[Former Member · Accepted solution]

Try creating a formula like this

@Date:

date(tonumber(),tonumber(),tonumber())

Now create a static date parameter with option allow range values

Now in the record selection write the condition like this

@Date={?DateParameter}

Hope this helps!

Raghavendra

[Former Member · Accepted solution]

Hi Raghavendra,

OK, I created the formula. But your next step is to create a static date parameter (with the option of allowing range values.)

But when I click on the "Value Field" list, the formula is not listed as an option. The month, day and year fields used to create the formula are listed there, but not the formula. So how do I create the static parameter?

[Former Member · Accepted solution]

You need not select any value field from the databse fields. Just create a static date parameter without any value field by selecting the option allow range values as true. Now this parameter will be prompted with start date and end date to enter from the calender.

Regards,

Raghavendra

[Former Member · Accepted solution]

Hi again Raghavendra,

That worked...thanks a lot. I had assumed you need to select something from the Value Fields list in order to create a parameter.

Now...I hate to press my luck, but would you be willing to show me how to incorporate the date range selected into my report title?

Something like: Appropriations paid between 1/4/2003 and 2/23/2005

If so, it'd be much appreciated. But if not, thanks for your help (you too Vinay)

jon

[Former Member · Accepted solution]

Try creating a new formula like this

"Appropriations paid between "& minimum({?DateParameter}) &" and "&maximum({?DateParameter})

Place this formula on th report to show the text on the report

Regards,

Raghavendra

[Former Member · Accepted solution]

Raghavendra,

Thanks...worked like a charm. I think this is the second time you've solved a problem for me...I may need to start sending you part of my pay check.

Thanks again,

Jon

[Former Member]

Hi,

Create a static date parameter and in selection formula

Place the condition as :

@formula>= {?DateParameter} and @formula<= {?DateParameter}

Regards,

Vinay