Im trying to find records where the search criteria will match one of 3
fields named
Group1, Group2 and Group3.  Im trying to use the following statement:

Where Coalesce(Group1, Group2, Group3) like ''%' + Edit1.Text + '%'''

This is returning an error
Any suggestions

Ben,

> Im trying to find records where the search criteria will match one of 3
> fields named
> Group1, Group2 and Group3.  Im trying to use the following statement:
>
> Where Coalesce(Group1, Group2, Group3) like ''%' + Edit1.Text + '%'''
>
> This is returning an error

As it should.

The correct syntax is
Coalesce(Group1,'') or (Coalesce(Group2,'') or Coalesce(Group3,'')

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=coalesce

Uli

Ben

>Im trying to find records where the search criteria will match one of 3
>fields named
>Group1, Group2 and Group3. Im trying to use the following statement:
>
>Where Coalesce(Group1, Group2, Group3) like ''%' + Edit1.Text + '%'''
>
>This is returning an error

Without seeing the error (hint hint) I can only guess. The guess is you have instances where Group1, Group2 and Group3 are all null so alter your COALESE to


Coalesce(Group1, Group2, Group3,'*') like ''%' + Edit1.Text + '%'''

Where '*' should be some text string that will NEVER occur in Group1, Group2 or Group3 - I generally use something like '¬!£$%^&*()¬' - I have yet to encounter that in any of mt databases

Roy Lambert [Team Elevate]

Uli


Good point, I was just fixing it as was

Roy Lambert

The correct syntax is as posted:
where coalesce(group1, group2, group3) = 101
The problem was that these fields are integer fields and the goal result I
tried was VarChar.

Sorry for this mistake
Thanx to All

"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:33BCDDC8-0B47-4C28-A7C3-18D9CD00E57E@news.elevatesoft.com...
> Ben
>
>>Im trying to find records where the search criteria will match one of 3
>>fields named
>>Group1, Group2 and Group3. Im trying to use the following statement:
>>
>>Where Coalesce(Group1, Group2, Group3) like ''%' + Edit1.Text + '%'''
>>
>>This is returning an error
>
> Without seeing the error (hint hint) I can only guess. The guess is you
> have instances where Group1, Group2 and Group3 are all null so alter your
> COALESE to
>
>
> Coalesce(Group1, Group2, Group3,'*') like ''%' + Edit1.Text + '%'''
>
> Where '*' should be some text string that will NEVER occur in Group1,
> Group2 or Group3 - I generally use something like '¬!£$%^&*()¬' - I have
> yet to encounter that in any of mt databases
>
> Roy Lambert [Team Elevate]

Ben,

> The problem was that these fields are integer fields and the goal result I
> tried was VarChar.

OK, then I completely misunderstood that.

Uli