SQL for List box search form

[mba_110]

Hi

I am struct with following code in below query involve 2 tables including numeric and text fields.

Option Compare Database
Option Explicit

Private Sub btnSearch_Click()
Dim SQL As String

SQL = "SELECT tblEmployees.EmpID, tblEmployees.FullName, tblEmployees.Surname, tblEmployees.Gender, tblEmployees.Nationality, tblResidentID.Resident_ID FROM tblEmployees INNER JOIN tblResidentID ON tblEmployees.EmpID = tblResidentID.ID;"
& " from tblemployees" _
& where [EmpID]"like '*" & me.txtSearch &"*' " _
& or [Fullname]"Like '*" & me.txtSearch &"*' " _
& or [Surname]"Like '*" & me.txtSearch &"*' " _
& or [Gender]"Like '*" & me.txtSearch &"*'" _
& or [Nationality] " Like '*" & me.txtSearch &"*'" _


End Sub

this is my row source for LstEmployees which is placed on frmSearch

Option Compare Database
Option Explicit

Private Sub btnSearch_Click()
Dim SQL As String

SELECT tblEmployees.EmpID, tblEmployees.FullName, tblEmployees.Surname, tblEmployees.Gender, tblEmployees.Nationality, tblResidentID.Resident_ID FROM tblEmployees INNER JOIN tblResidentID ON tblEmployees.EmpID = tblResidentID.ID;"

End Sub

One search text box on form linked to LstEmployees to filter result Similar to search box text or numbers with in above tables fields.

You kind help is required to resolve the query.

 

[Uncle Gizmo]

I can't quite grasp the essence of your question. So I'm bumping it up maybe somebody else to have a look. But I'm thinking shouldn't "tblResidentID" also be in the from statement?

 

[arnelgp]

remove tbe semi colon ) on your first SQL string

 

[MarkK]

Also, this looks a lot like another thread you can find here, which may provide you with more ideas about how to do what you are doing.
hth
Mark