Benjamins said:
Hi,
I have a table(EmpCode) containg a list of item
Below is an example of the list
Andy * Leon
Andy * Peter
Andy * Leon * Peter
I have create a form for the filter to allow user to search for the
name. Below is the example of the SQL
SELECT * FROM [EmpCode] WHERE [Fldcode] Like '*" & strFilter & "*'".
I know that *, # and ? are wildcards ...
Only if your database is set to use ANSI SQL syntax rather than JET SQL
syntax. In A2000 and A2002, the default was changed to be ANSI SQL,
meaning the % and _ are now the wildcards instead of * and ?. Older
databases will use JET SQL syntax, and you can set new databases to use
the older syntax as well.
but due to company policy, i
cannot change the character thus is there a way to filter that only
Andy * Peter appear?
I second Jeff's advice, but to search for a wildcard character, I
believe you have to escape it by putting it in brackets, like this;
Like "[*]" & strFilter & "[*]"
However, this won't work ... you do need to use wildcards, like this:
Like "*[*]" & strFilter & "[*]*".
However, even this won't work. In your sample data, the name "Peter"
never has an ending delimiter, and "Andy" never has a beginning
delimiter. What you have to do is destroy this query's performance even
more by surrounding the field data with delimiters:
WHERE "*" & FldCode & "*" like "*[*]" & strFilter & "[*]*"
So now, when you search for "Peter", you get both records.
But we're not done yet. I just noticed that you asked how to search for
"Andy * Peter" rather than just a single name. Assuming that strFilter
will contain "Andy*Peter" you need to use the Replace function to put
the brackets around the wildcard:
WHERE "*" & FldCode & "*" like "*[*]" & Replace(strFilter,"*","[*]") &
"[*]*"
Now this works to return the second record containing "Andy*Peter". It
will even return a record containing "Bob*Andy*Peter*Benjamins"
Now, we might not even be done at this point. Do you want to return Andy
* Leon * Peter when searching for "Andy*Peter"? And do you want to
return any records when searching for "Peter*Andy"?
If so, you're going to have to resort to VBA to break up the search
string and construct a query with multiple AND'ed LIKE searches that
looks like this:
WHERE "*" & FldCode & "*" like "*[*]Andy[*]*"
AND "*" & FldCode & "*" like "*[*]Peter[*]*"
Strangely enough, this last scenario is the only that is just as hard
with normalized data as it is with your denormalized data. The
recommended way to store this data is in a related table. For example,
say that each EmpCode has an id to identify it. The related table would
look like this:
id FldCode
1 Andy
1 Peter
2 Andy
2 Leon
3 Andy
3 Leon
3 Peter
One of the ways to get the two ids (1 and 3) that contain both Andy and
Peter looks like this:
SELECT distinct id
FROM EmpCode as e
where
EXISTS (select * from Empcode where FldCode='Andy'
and id = e.id)
AND
EXISTS (select * from empcode where fldcode='Peter'
and id = e.id)
This isn't really likely to performance much better than the solution
proposed above for your sample denormalized data. It would also have to
be built using VBA.
However, when it comes to searching for a single name, this design wins
hands-down:
SELECT distinct id
FROM EmpCode as e
where FldCode ='Andy'
