Concatenating two fields to form a string to compare against a UCase ?

Im trying to use a UCase LIKE Search...

This is okay if I only want to search a word/words from one field, but how do I do it if I need to join 2 fields ?

Eg. I have 2 fields CustomerFirstname and CustomerSurname

What happens if the user types into the search "VisualBasic Kid"
where VisualBasic is the CustomerFirstname and Kid is the CustomerSurname ?

It won't find anything...

Maybe someone can take a look at my string and tell me where I'm going wrong...


Select * From Customer Where UCase(CustomerSurname) LIKE
'%" & UCase(txtSearch) & "%' OR (UCase(CustomerFirstname) &
UCase(CustomerSurname)) LIKE '%" & UCase(txtSearch) & "%'", db,
adOpenStatic, adLockOptimistic


Thanks for any help once again

well, an OR condition is not what you want. You want to use AND.

where field1 LIKE 'this' AND field2 LIKE 'that' (both conditions must be true).


Or, if you truly want to concatenate the strings into one valuen (with a space between words), you could do this:

strString = strString & " " & strString2

I had tried AND but it didnt seem to work.

May try truly concatenating the strings ...

Why not try this Sample Query. You can modify it according to your requirement.

SELECT Customer.FName+' '+Customer.LName FROM Customer
WHERE (((Customer.FName+' '+Customer.LName)="zy_abc abc"))

Please Note: Double Space between Customer FNAME & Customer LNAME.

What you could do, (similar to what was posted last) is combine the two fields you want to search for. Then, compare this newly creating string from your recordset. So it would now consist of "field1 <space> field2". And compare it with with one LIKE statement, which would be your condition string such as "word1 <space> "word2", etc.