Multiple parameter search problem 1

[LRBFAN]

Using Foxpro DB and CR 2008. Two tables (Students and Payments) linked by Client_no.

Report has 4 parameters : ?Date Range, ?Amount, ?Last Name and ?Receipt Number

The goal of the report is to list student payment info based on the results of 3 different search possibilities:

1. by Date Range AND Amount....or
2. by all or starting letters of Last Name....or
3. by Receipt Number

I'm not retrieving any records when I search by Receipt Number even though I am entering valid numbers returned from the other 2 searches that work fine. Receipt Number is a STRING.

Here's my record selection formula:

{payments.account} = "4000" and
{students.program} in ["3M","6M","9M","MO","WR"] and

(({payments.date} = {?Date Range} and {payments.amount} = {?Amount} and {?Amount} <> 0.00)
or ({@Start Date Range} = date(9999,9,9) and {@End Date Range} = date(9999,9,9) and
{students.lname} startswith(UPPERCASE({?Last Name})) and trim({?Last Name}) <> ')
or ({@Start Date Range} = date(9999,9,9) and {@End Date Range} = date(9999,9,9) and
{payments.receiptno} = {?Receipt Number} and trim({?Receipt Number}) <> '))

Thanks for any help you can provide.

 

[lbass]

{payments.account} = "4000" and
{students.program} in ["3M","6M","9M","MO","WR"] and
(
(
{payments.date} = {?Date Range} and
{payments.amount} = {?Amount} and
{?Amount} <> 0.00
)or
(
{@Start Date Range} = date(9999,9,9) and
{@End Date Range} = date(9999,9,9) and
{students.lname} startswith UPPERCASE({?Last Name}) and
trim({?Last Name}) <> '
)or
(
{@Start Date Range} = date(9999,9,9) and
{@End Date Range} = date(9999,9,9) and
{payments.receiptno} = {?Receipt Number} and
trim({?Receipt Number}) <> '
)
)

This looks right to me, so I wonder about the content of your nested formulas. Are they like:

minimum({?Date Range})

Does the receiptno display the same way in both field and parameter entry?

Remove (comment it out) the record selection formula and instead copy it into a regular formula and place it in the detail section. Then comment out portions to see which part is returning a false.

-LB

 

[LRBFAN]

Thank you LB for this true/false testing tip. This will come in handy many times I'm sure.

After writing/deleting a LONG sob story of all my efforts, the lightbulb came on.

{payments.receiptno} and {?Receipt Number} were displaying the same way but still showing FALSE even though they looked the same so that told me there was something in {payments.receiptno} I couldn't see......like SPACES.

After adding trim({payments.receiptno}) to the formula they matched!!

Thank you once again for all your help LB. Much appreciated.