>>i have set ansi off now. still pulling more records than it should. here's the sql statement
>>
>>SELE pat_lname, pat_fname, pat_dob, pat_cdcid, recno() as cRec FROM PATIENT_VIEW WHERE ALLT(PATIENTS.PAT_LNAME) = ALLT(THISFORM.TXT_PATLNAME.VALUE) GROUP BY PAT_LNAME INTO CURSOR CURPAT
>
>
>lcValue=ALLT(THISFORM.TXT_PATLNAME.VALUE)
>Select pat_lname, pat_fname, pat_dob, pat_cdcid, recno() as nRec ;
FROM PATIENT_VIEW WHERE PAT_LNAME =m.lcValue INTO CURSOR CURPAT
>
>Why do you want to group by last name?
This SQL expects to have index on pat_lName to be optimizable. If you have index on upper(pat_lName) use upper(pat_lName) instead. Don't use alltrim here. I don't think, you have an index on alltrim(pat_lName) and if you do, it's not good.
If it's not broken, fix it until it is.
My Blog