I have a search form which takes a users full name, i then want to query a 

table which has the fields strFirstName and strLastName.



How can i search the table 'combining these two fields into one field'.



i.e. i figure something kind of like this but am not sure how to go about 

it.



searchterm = "Fred Bloggs"



SELECT * FROM tbl_user WHERE (strFirstName & " " & strLastName) = '" & 

searchterm & "' ORDER BY strFirstName

There might be a way to do it the way you describe, but I don't know of 

it.  However, there are some things you can do.  When I'm searching based 

on a search criteria string, I use the split function to put all the 

search criteria in an array.  Then you can build an sql statement by 

looping thru the elements of that array.  



something like this..



strCrit()=split(strSearchString)

(get the number of elements of the array, it's something like UBound

(strCrit,1), set intNumCriteria to that value



strSql="SELECT * FROM tbl_user WHERE"



for a=0 to intNumCriteria



    strSql=strSql & "fldFirstName LIKE ' " strCrit(a) " ' OR fldLastName 

LIKE ' " strCrit(a) " ' "



    if a<intNumCritere then 

  

        strSql=strSql & "OR"

    else



        strSql=strSql & "ORDER BY fldFirstName;"

    end if



next



Now, I haven't tested this exact block of code, but I've done similar 

stuff a lot, and this basic approach should work.  Let me know.

You may want to consider using the split or join function.  If the db has 2

fields (first and Lastname)



Dim arrString

Dim searchterm

Dim Arrfirst

Dim arrlast

...after you get the user to input searchterm

arrString = Split(searchterm," ")

...now arrString is an array from 0 to x depending on how many spaces are

found.

...before your SQL you can test to see if array element 3 exists..you may

have a John Van Halen

...determine the lastname by the array (probably the last) 

...I would then set the query variables based on the array item to make it

easy to read

...and then query

Arrfirst = arrString(0)



SQL = "SELECT * from table WHERE dbfirstname=" & Arrfirst 

SQL = SQL & " and dblastname=" & Arrlast

SQL = SQL & " order by dbfirstname"  '...why firstname??



If db is one field, use a join of the two form fields.