Robert Sorrells
|
Posted: 07/13/2003, 5:59 PM |
|
Hello,
When on a filtered grid, the datasouce contains large list which eventually
is converted into a "While" statement. It has field names on the left, and
filter names on the right. Somewhere along the way, the filter names such as
{s_LName} is converted to values prior to the SQL statement's execution.
What I'm trying to do is, within the DataSource list, to add an "OR"
parentheses to one of the filter expressions, and reference only the search
name and a constant.
For example, say that one of the fields is "AGE" and the search field name
is "s_AGE". I either want to type in the age, or type in 0 to select all.
The first line of the filter would already be
AGE like '%{s_AGE}%'
So what I want is
(AGE like '%{s_AGE}%' OR
{s_AGE} = 0)
I added the 2nd line as an expression, but CCS isn't substituting {s_AGE}
with the number 0 like it did on the first line in the SQL statement.
Any suggestions?
If I can't do this in the DataSource list, how to you get the s_AGE value
from the filter in "Before Build Select" ?
Thanks!
Bob
|
|
|
DonB
|
Posted: 07/14/2003, 6:01 AM |
|
Sounds like your database has "0" where there is "no age given" and that is
why you need to modify the query to select records where there is an age or
where there is a zero in the age column.
I have a fundamental database design issue with this, as the "purist's"
approach is to have a NULL where there is no age, but this won't change the
solution.
What you need is to drop the WHERE clause if s_AGE is zero, so in the
BeforeExecuteSelect event, set the Dataaource Where parameter to an empty
string. This removes the filter when s_AGE is a zero so the select returns
all rows.
DonB
"Robert Sorrells" <rsorrellsU@KNOWmindspring.com> wrote in message
news:besvaa$1bb$1@news.codecharge.com...
> Hello,
>
> When on a filtered grid, the datasouce contains large list which
eventually
> is converted into a "While" statement. It has field names on the left,
and
> filter names on the right. Somewhere along the way, the filter names such
as
> {s_LName} is converted to values prior to the SQL statement's execution.
>
> What I'm trying to do is, within the DataSource list, to add an "OR"
> parentheses to one of the filter expressions, and reference only the
search
> name and a constant.
>
> For example, say that one of the fields is "AGE" and the search field name
> is "s_AGE". I either want to type in the age, or type in 0 to select
all.
>
> The first line of the filter would already be
>
> AGE like '%{s_AGE}%'
>
> So what I want is
>
> (AGE like '%{s_AGE}%' OR
> {s_AGE} = 0)
>
> I added the 2nd line as an expression, but CCS isn't substituting {s_AGE}
> with the number 0 like it did on the first line in the SQL statement.
>
> Any suggestions?
>
> If I can't do this in the DataSource list, how to you get the s_AGE value
> from the filter in "Before Build Select" ?
>
> Thanks!
> Bob
>
>
>
|
|
|
|