>This code is in a form where we give users the question: Select groups of the following form ~~~~~~-~~~~~~.....
>where the characters 1-3 represent the group and characters 4-6 represent the subgroup. They can put in a * (%) or ?(_) anywhere.
>
>I've been testing for the following cases, though there may be others that I haven't accounted for. They want to able to get:
>1) all records where the 1st 2 characters is something, regardless of the 3rd character of the specific group, regardless of the subgroup (like AJ* which should show AJ as well as AJA)

set exact off
set ansi off
select * from youtable where cGroup1=lcGroup && if index is on Group, if it's on upper(group) use upper(group)

>2) all records where the group is AJ and we do not want to include AJA
select * from youtable where cGroup1==lcGroup

>3) all records of a specific group, only if there is a subgroup

Well, a little bit tricky: where cGroup1=lcGroup and cGroup2<>space(3)

>4) all records of a specific group, only if there is no subgroup
where cgroup1=lcGroup and cgroup2=space(3)

>5) all records of a specific group and a specific subgroup
where cGroup1=lcGroup1 and cGroup2=lcGroup2, where lcGroup1 and 2 are both 3 letter chars.

>
>I've worked around it by doing the following, but I'm still curious why the 2nd case and the otherwise are not equivalent
>
>jcItem=the 6 characters
> DO CASE
> CASE RIGHT(TRIM(jcItem),1)='%' AND LEN(TRIM(jcItem))<=3 &&AJ%
> jcWhere=" WHERE cGroup1 LIKE '"+TRIM(jcItem)+"'"
> CASE !'_'$jcItem AND !'%'$jcItem &&no wildcard specs
> jcWhere=" WHERE cGroup1+cGroup2 LIKE '"+jcItem+"'" &&no trim since spaces are significant
> OTHERWISE
> jcItem1=TRIM(LEFT(jcItem,3))
> jcItem2=TRIM(RIGHT(jcItem,3))
> jcWhere=" WHERE cGroup1 LIKE '"+jcItem1+"' AND cGroup2 LIKE '"+jcItem2+"'"
> ENDCASE
> jcSQL="SELECT * FROM xxx"+jcWhere
>
>Thanks for taking the time to respond
>Pessy Butler