Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 4 of 4 total
Thread Filter issue
Wed, May 19 2021 3:20 PMPermanent Link

Peter van Mierlo

EVENTSOFT

Hi

It has been a while that i uses dbisam and i have a small filter issue

My table has 2 fields:
- Name
- Text1
- Text2

See screen example contains test data but in real the values for :
field TEXT1 contains the firstname of a person
field TEXT2 contains the lastname of a person

So for this test a combined search has not direct value but it must be possible to search for
a value in TEXT1 OR TEXT2

Therefore i use the following filter:

dmMain.WristbandGroup.Filter:='LOWER(Text1) like LOWER('+QuotedStr('%'+SearchBox.Text+'%') +') OR' + 'LOWER(Text2) like LOWER('+QuotedStr('%'+SearchBox.Text+'%') +')';

When i search for the value 18 i got NO results with the above filter

When i create a filter only for TEXT2 like:
dmMain.WristbandGroup.Filter:='LOWER(Text2) like LOWER('+QuotedStr('%'+SearchBox.Text+'%') +')';

and searcht for value 18 i got 1 record

What am i missing...must i create a index with both fields or is there a error in the syntax?

Regards Peter



Attachments: iPolsband_D9kUkG5Mmw.png
Thu, May 20 2021 4:01 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Peter


The only thing I can see that might be wrong is that there doesn't seem to be a space between OR and LOWER but I'd expect that to throw an error.

What happens if you type in the filter directly eg set the filter to

LOWER('Text1) like LOWER('%18'%') OR LOWER(Text2) like LOWER('%18%')

The other thing I like to do with compound filters is use brackets so I know what the execution order will be eg

(LOWER('Text1) like LOWER('%18%')) OR (LOWER(Text2) like LOWER('%18%'))



Roy Lambert
Thu, May 20 2021 7:39 AMPermanent Link

Peter van Mierlo

EVENTSOFT

Avatar

Hi Roy,

Thanks for the answer AND the solution. The space you mention between the OR and LOWER
was the issue. It was OR' and with OR ' it's working.

Thanks

Roy Lambert wrote:

Peter


The only thing I can see that might be wrong is that there doesn't seem to be a space between OR and LOWER but I'd expect that to throw an error.

What happens if you type in the filter directly eg set the filter to

LOWER('Text1) like LOWER('%18'%') OR LOWER(Text2) like LOWER('%18%')

The other thing I like to do with compound filters is use brackets so I know what the execution order will be eg

(LOWER('Text1) like LOWER('%18%')) OR (LOWER(Text2) like LOWER('%18%'))



Roy Lambert
Thu, May 20 2021 9:20 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Peter


Great - I'd never seen a DBISAM command ORLOWER which made me suspicious Smiley

Unless you have your own error trapping code which prevented it being surfaced I think you should report it to Tim. It may be WAD but I'd say its a bug (or your code).

Roy Lambert
Image