I have a form used to present a list of "qualifications", filtered by qualification type. The form has a search text box in the header to find a person's qualification. Initially when opened the form displays the relevant records. When the first character is entered the list should filter, however all records disappear, despite the char entered being a value in the set. I have tested the SQL independently as a standalone query and it shows the expected result set.
The vba query used in the form acting on the search text is:
The intQTypeID is a value on the form representing the qualification type : Me.QTypeID - and shows the value used in the filter on opening the form.
The query above works when the ID is run with a intQTypeID from outside the form, however when running the search it reports the expression has no value - referring to: intQTypeID = Me.QTypeID
Hope it is a simple oversight on my part. Thanks
The vba query used in the form acting on the search text is:
Code:
strFilteredList = "SELECT tblQual.*, tluQual.QualType, tluQual.QualAuthority, [FirstName]+ "" "" +UCase([LastName]) AS PersonName, tblPersons.FirstName, tblPersons.LastName" & _
" FROM tblPersons RIGHT JOIN (tblQual LEFT JOIN tluQual ON tblQual.QualTypeID = tluQual.QualTypeID)" & _
" ON tblPersons.PersonID = tblQual.PersonID" & _
" WHERE ([tblQual].[QualTypeID] = " & intQTypeID & _
" AND [FirstName] LIKE "" * " & Me.txtFilterPerson.Value & " * "")" & _
" OR ([tblQual].[QualTypeID] = " & intQTypeID & _
" AND [LastName] Like "" * " & Me.txtFilterPerson.Value & " * "")" & _
" ORDER BY [FirstName]+"" ""+UCase([LastName]);"
The intQTypeID is a value on the form representing the qualification type : Me.QTypeID - and shows the value used in the filter on opening the form.
The query above works when the ID is run with a intQTypeID from outside the form, however when running the search it reports the expression has no value - referring to: intQTypeID = Me.QTypeID
Hope it is a simple oversight on my part. Thanks