Depending on the fields the user fills in, I need to build
a sqlpass though statement that search fields based on the beginning of the string:
Example: The user enters KE in a textfield which is stored to gcLast
I want to be able to return a result set where all the last names start with KE
Here is one of the ways I tried
SQLEXEC(gnConnHandle,[select * from patients where lastname like '?gcLast%'])
I though I just about had it with this one:
select * from patients
where {fn locate(?gcLast,lastname)}>0
AND {fn locate(?gcFIRST,firstname)}>0
But I gues locate finds a match anywhere in the string, thus if I enter
KE for the lastName
R for the firstname
it will find records:
KELLY, KIRK
KELLY, ROGER
When I really just wanted it to find KELLY, ROGER
Help Please