I have a query that I made using VBA;
Code:
Dim strSql As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strCriteria As String
Dim strFields As String
strFields = "[tblPatient].[PatientID], [tblPatient].[FirstName] & ' ' & [tblPatient].[LastName] AS PatientName, DateOfBirth, [tblPatient].[FacilityID]"
strCriteria = ""
If Not IsNull(Me.PatientName) Then
strCriteria = " WHERE [PatientName] Like '*" & [Forms]![frmDataExtraction]![PatientName] & "*'"
End If
strSql = "SELECT " & strFields & " FROM tblPatient" & strCriteria
qdf.SQL = strSql
When running this the query cannot determine what field the PatientName belongs to if there is a value in the textbox "PatientName", but if there is nothing in that text box the query is made with the PatientName field populated.
What can I do to fix this so that the strCriteria works properly and the query has a field with the proper criteria.