MS Acces Query Where Clause on Fly with condition
I am creating on fly “Where condition for a query .
I am using PatHartman example:
**************************
Private Sub cmdViewResults_Click()
On Error GoTo Err_Proc
Call BuildSQL
DoCmd.OpenForm "frmSearchResults", , , , , , Me.name
Me.Visible = False
Exit_Proc:
On Error GoTo 0
Exit Sub
Err_Proc:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdViewResults_Click of VBA Document Form_frmClientSearch"
End Select
End Sub
Public Sub BuildSQL()
Dim strSQL As String
Dim strSelect As String
Dim strSelectForExport As String
Dim strWHERE As String
Dim strCondition As String
Dim strOrderBy As String
strSelect = "Select * from qClientList "
strSelectForExport = "Select * from qClientListExport "
strOrderBy = " ORDER BY FullName"
strWHERE = ""
If Me.txtFirstName & "" = "" Then
Else
strWHERE = "FirstName Like " & QUOTE & Me.txtFirstName & "*" & QUOTE
End If
If Me.txtLastName & "" = "" Then
Else
strCondition = "LastName Like " & QUOTE & Me.txtLastName & "*" & QUOTE
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtcity & "" = "" Then
Else
strCondition = "City Like " & QUOTE & Me.txtcity & "*" & QUOTE
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtaddress & "" = "" Then
Else
strCondition = "Address Like " & QUOTE & Me.txtaddress & "*" & QUOTE
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkBUP = True Then
strCondition = "BUP = True"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboCareMgrID & "" = "" Then
Else
strCondition = "CareMgrID = " & Me.cboCareMgrID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboGenderID & "" = "" Then
Else
strCondition = "GenderID = " & Me.cboGenderID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtZip & "" = "" Then
Else
strCondition = "Zip = '" & Me.txtZip & "'"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboRaceID & "" = "" Then
Else
strCondition = "RaceID = " & Me.cboRaceID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboDiagnosis & "" = "" Then
Else
strCondition = "Diagnosis = '" & Me.cboDiagnosis & "'"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtpid & "" <> "" Then
strCondition = "PID = '" & Me.txtpid & "'"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtClientID & "" <> "" Then
strCondition = "ClientID = " & Me.txtClientID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtems & "" <> "" Then
strCondition = "EMS = '" & Me.txtems & "'"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtPhone & "" <> "" Then
strCondition = "(Phone = '" & Me.txtPhone & "' OR CellPhone = '" & Me.txtPhone & "')"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboCounty & "" <> "" Then
strCondition = "County = '" & Me.cboCounty & "'"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboLevelID & "" <> "" Then
strCondition = "LevelID = " & Me.cboLevelID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboTier & "" <> "" Then
strCondition = "PCATier = " & Me.cboTier
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboProgramID & "" = "" Then
Else
strCondition = "ProgramID = " & Me.cboProgramID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
'Funding
If Me.chkT19 = True Then
strCondition = "T19 = True"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkSelf = True Then
strCondition = "Self = True"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkMFP = True Then
strCondition = "MFP = True"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkNOTT19 = True Then
strCondition = "Not(T19 = True)"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkNOTSelf = True Then
strCondition = "Not (Self = True)"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkNOTMFP = True Then
strCondition = "Not(MFP = True)"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
Select Case Me.fraStatus
Case 1
strCondition = "StatusID = 1318" 'Open
Case 2
strCondition = "StatusID = 1319" 'Closed
Case 4
strCondition = "StatusID = 1427" 'Pending
Case Else
strCondition = ""
End Select
If strWHERE = "" Then
If strCondition = "" Then
Else
strWHERE = strCondition
End If
Else
If strCondition = "" Then
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
'query for form/report
If strWHERE = "" Then
strSQL = strSelect
Else
strSQL = strSelect & " WHERE " & strWHERE
End If
strSQL = strSQL & strOrderBy
Me.txtQuery = strSQL
'query for Excel
If strWHERE = "" Then
strSQL = strSelectForExport
Else
strSQL = strSelectForExport & " WHERE " & strWHERE
End If
strSQL = strSQL & strOrderBy
Me.txtQueryExport = strSQL
End Sub
**************************
I have a question I have another query e.g. “Qry_Exception_Race” that have some exception records in.
I used this query in strCondition part below.
But execution is very slow I was wondering can excecute that query before strCodnition
I am not sure is this possible, I need something as this.
…….
If Me.cboRaceID & "" = "" Then
Else
strCondition = " Not RaceID In (Select Qry_Exception_Race.Race_ID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
……………
But execution is very slow I was wondering can execute that query “Qry_Exception_Race” before or create query def before and create some recordset with Race_ID and use it in strCondition to get on speed something like:
…….
If Me.cboRaceID & "" = "" Then
Else
strCondition = " Not RaceID In (rstRace.Race_ID)
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
……………
Any suggestion?
I am using PatHartman example:
**************************
Private Sub cmdViewResults_Click()
On Error GoTo Err_Proc
Call BuildSQL
DoCmd.OpenForm "frmSearchResults", , , , , , Me.name
Me.Visible = False
Exit_Proc:
On Error GoTo 0
Exit Sub
Err_Proc:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdViewResults_Click of VBA Document Form_frmClientSearch"
End Select
End Sub
Public Sub BuildSQL()
Dim strSQL As String
Dim strSelect As String
Dim strSelectForExport As String
Dim strWHERE As String
Dim strCondition As String
Dim strOrderBy As String
strSelect = "Select * from qClientList "
strSelectForExport = "Select * from qClientListExport "
strOrderBy = " ORDER BY FullName"
strWHERE = ""
If Me.txtFirstName & "" = "" Then
Else
strWHERE = "FirstName Like " & QUOTE & Me.txtFirstName & "*" & QUOTE
End If
If Me.txtLastName & "" = "" Then
Else
strCondition = "LastName Like " & QUOTE & Me.txtLastName & "*" & QUOTE
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtcity & "" = "" Then
Else
strCondition = "City Like " & QUOTE & Me.txtcity & "*" & QUOTE
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtaddress & "" = "" Then
Else
strCondition = "Address Like " & QUOTE & Me.txtaddress & "*" & QUOTE
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkBUP = True Then
strCondition = "BUP = True"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboCareMgrID & "" = "" Then
Else
strCondition = "CareMgrID = " & Me.cboCareMgrID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboGenderID & "" = "" Then
Else
strCondition = "GenderID = " & Me.cboGenderID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtZip & "" = "" Then
Else
strCondition = "Zip = '" & Me.txtZip & "'"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboRaceID & "" = "" Then
Else
strCondition = "RaceID = " & Me.cboRaceID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboDiagnosis & "" = "" Then
Else
strCondition = "Diagnosis = '" & Me.cboDiagnosis & "'"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtpid & "" <> "" Then
strCondition = "PID = '" & Me.txtpid & "'"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtClientID & "" <> "" Then
strCondition = "ClientID = " & Me.txtClientID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtems & "" <> "" Then
strCondition = "EMS = '" & Me.txtems & "'"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtPhone & "" <> "" Then
strCondition = "(Phone = '" & Me.txtPhone & "' OR CellPhone = '" & Me.txtPhone & "')"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboCounty & "" <> "" Then
strCondition = "County = '" & Me.cboCounty & "'"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboLevelID & "" <> "" Then
strCondition = "LevelID = " & Me.cboLevelID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboTier & "" <> "" Then
strCondition = "PCATier = " & Me.cboTier
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboProgramID & "" = "" Then
Else
strCondition = "ProgramID = " & Me.cboProgramID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
'Funding
If Me.chkT19 = True Then
strCondition = "T19 = True"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkSelf = True Then
strCondition = "Self = True"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkMFP = True Then
strCondition = "MFP = True"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkNOTT19 = True Then
strCondition = "Not(T19 = True)"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkNOTSelf = True Then
strCondition = "Not (Self = True)"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkNOTMFP = True Then
strCondition = "Not(MFP = True)"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
Select Case Me.fraStatus
Case 1
strCondition = "StatusID = 1318" 'Open
Case 2
strCondition = "StatusID = 1319" 'Closed
Case 4
strCondition = "StatusID = 1427" 'Pending
Case Else
strCondition = ""
End Select
If strWHERE = "" Then
If strCondition = "" Then
Else
strWHERE = strCondition
End If
Else
If strCondition = "" Then
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
'query for form/report
If strWHERE = "" Then
strSQL = strSelect
Else
strSQL = strSelect & " WHERE " & strWHERE
End If
strSQL = strSQL & strOrderBy
Me.txtQuery = strSQL
'query for Excel
If strWHERE = "" Then
strSQL = strSelectForExport
Else
strSQL = strSelectForExport & " WHERE " & strWHERE
End If
strSQL = strSQL & strOrderBy
Me.txtQueryExport = strSQL
End Sub
**************************
I have a question I have another query e.g. “Qry_Exception_Race” that have some exception records in.
I used this query in strCondition part below.
But execution is very slow I was wondering can excecute that query before strCodnition
I am not sure is this possible, I need something as this.
…….
If Me.cboRaceID & "" = "" Then
Else
strCondition = " Not RaceID In (Select Qry_Exception_Race.Race_ID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
……………
But execution is very slow I was wondering can execute that query “Qry_Exception_Race” before or create query def before and create some recordset with Race_ID and use it in strCondition to get on speed something like:
…….
If Me.cboRaceID & "" = "" Then
Else
strCondition = " Not RaceID In (rstRace.Race_ID)
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
……………
Any suggestion?
ASKER
Somehow this Not Exists shows nothing - no records, on another spot where I need to use Exists it shows me wrong number of records.???
ID column is Indexed.
Not sure why this happening
ID column is Indexed.
Not sure why this happening
Hi,
you should add the table alias of the main query to RaceID, I did not knew it from your query. In your original it is named only "RaceID" and as the same RaceID is also in the inner query it may select the wrong field. So if the main query uses an alias i.e. "X" it would then be "...WHERE Qry_Exception_Race.Race_ID
Cheers,
Christian
you should add the table alias of the main query to RaceID, I did not knew it from your query. In your original it is named only "RaceID" and as the same RaceID is also in the inner query it may select the wrong field. So if the main query uses an alias i.e. "X" it would then be "...WHERE Qry_Exception_Race.Race_ID
Cheers,
Christian
ASKER
Yes I got it but speed is the same slow even more slow then when I used Not IN ????
Hi,
it would be helpful if you could use the Immediate window of VBA to print the complete SQL string and copy it here. Also you should copy the SQL text of this "Qry_Exception_Race" because this could also be a performance bottleneck.
Moreover to analyze the reason you should check all the fields you use in your VBA if they have indexes. As you use LIKE but only with an asterisk at the end they would be able to use an index (not possible if you would add an asterisk at the beginning of the search string).
Best thing would be if you could upload a demo database with some demo records. If this one is connected to a database server as backend the performance could also be a problem of the way you query it. The asterisk says that it is an Access query, if you would have i.e. SQL Server as backend the joker would be "%" instead. If you have a database server in the backend then you should assemble the WHERE string as filter string and use it on the server to assemble the complete SQL String there and execute it there, then the performance would be very much better.
Cheers,
Christian
it would be helpful if you could use the Immediate window of VBA to print the complete SQL string and copy it here. Also you should copy the SQL text of this "Qry_Exception_Race" because this could also be a performance bottleneck.
Moreover to analyze the reason you should check all the fields you use in your VBA if they have indexes. As you use LIKE but only with an asterisk at the end they would be able to use an index (not possible if you would add an asterisk at the beginning of the search string).
Best thing would be if you could upload a demo database with some demo records. If this one is connected to a database server as backend the performance could also be a problem of the way you query it. The asterisk says that it is an Access query, if you would have i.e. SQL Server as backend the joker would be "%" instead. If you have a database server in the backend then you should assemble the WHERE string as filter string and use it on the server to assemble the complete SQL String there and execute it there, then the performance would be very much better.
Cheers,
Christian
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
optimal solution provided
you should make sure that you set an index on the ID column of both tables. Moreover you should always try to avoid the "IN" clause and use "EXISTS" instead. The reason is that with "IN" all the records are created in a result table and then it is compared, with "EXISTS" only the existance of the record is checked and no result table (the one in the brackets) will be needed. So use instead:
Open in new window
In most cases you can always use Exists instead of In.
Cheers,
Christian