I am creating a search form where an end user can enter a field in a text box, then hit a command button and values are returned. The problem is I have two fields that are text (cardholder and approving official) values are being returned as numeric in the form. I am expecting the form to display the names not numeric values.
Fields from Issues table
cardholder - text
approving official - text
RequisitionNumber - text
Transactionnumber - text
Category - text
Status - text
Below is the copy of the code
Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
'Constant Select statement for the RowSource
strSQL = "SELECT issues.id, issues.ApprovingOfficial, issues.CardHolder, issues.RequisitionNumber, issues.transactionnumber,issues.category, issues.status " & _
"FROM issues"
strWhere = "WHERE"
'strOrder = "ORDER BY issues.id;"
'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.CardHolder) Then '<--If the textbox txtcardholder contains no data THEN do nothing
strWhere = strWhere & " (issues.cardholder) Like '*" & Me.CardHolder & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
If Not IsNull(Me.ApprovingOfficial) Then
strWhere = strWhere & " (issues.approvingofficial) Like '*" & Me.ApprovingOfficial & "*' AND"
End If
If Not IsNull(Me.RequisitionNumber) Then
strWhere = strWhere & " (issues.RequisitionNumber) Like '*" & Me.RequisitionNumber & "*' AND"
End If
If Not IsNull(Me.Category) Then
strWhere = strWhere & " (issues.category) Like '*" & Me.Category & "*' AND"
End If
If Not IsNull(Me.Status) Then
strWhere = strWhere & " (issues.status) Like '*" & Me.Status & "*' AND"
End If
'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
'Pass the SQL to the RowSource of the listbox
Me.lstCustInfo.RowSource = strSQL & " " & strWhere & "" & strOrder
End Sub