SQL code to filter selection in combo boxes

A

angogwanon

I am trying to setup a Search Button with four possible variables. The
results need to show all records containing the variables entered.

I have four different tables in my database and a main form that allows the
person to add new records or search the four queries. The person selects the
query the wish to search, then types in the search information. I made sure
the names of the search fields are the same in all four queries (company,
web, city, and state). The state field is a combo box.

I am able to set up the code to get the WHERE part of the query filter.
However, I am unable to figure out how to execute the query with the WHERE
filter. Below is the code I have so far:

Dim strWhere As String
Dim strQuery As String

If Me.Sourcequery.Value >= 1 Then

strQuery = Me.Sourcequery.Value

'Filtering each text box for values
If Not IsNull(Me.txtCompName) Then
strWhere = strWhere & "([Company] like ""*" & Me.txtCompName &
"*"")"
End If

If Not IsNull(Me.txtWeb) Then
strWhere = strWhere & "([Web] like ""*" & Me.txtWeb & "*"")"
End If

If Not IsNull(Me.txtCity) Then
strWhere = strWhere & "([City] like ""*" & Me.txtCity & "*"")"
End If

If Not IsNull(Me.cboState) Then
strWhere = strWhere & "([State]= """ & Me.cboState & """)"
End If

What is the code to open the query with the completed strWhere as the
filter? I appreciate any help I can get. If there is another way, please
let me know. While I have used VB in the past, it has been awhile.
 
A

Allen Browne

The WHERE clause needs AND between the various phrases.

The most flexible way to do that is to tack AND on the end of each one, and
chop off the trailing one at the end.

Dim lngLen as Long
If Not IsNull(Me.txtCompName) Then
strWhere = strWhere & "([Company] like ""*" & Me.txtCompName & "*"")
AND "
End If
If Not IsNull(Me.txtWeb) Then
strWhere = strWhere & "([Web] like ""*" & Me.txtWeb & "*"") AND "
End If
'etc
lngLen = Len(strWhere) - 5 'Without the trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

Once you get this WHERE clause, you can apply it as the Filter of a form
like this:
Forms!Form1.Filter = strWhere
Forms!Form1.FilterOn = True
Or you can open a report like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
Or, if you want to build the whole SQL statement and apply it to Query1:
Dim strSql
strSql = "SELECT * FROM Table1 WHERE " & strWhere & ";"
CurrentDb.QueryDefs("Query1").SQL = strSql
 
A

angogwanon

Thank you very much for answering my question.

Everything works until I get to the last statement:
CurrentDb.QueryDefs("Query1").SQL = strSql

However, when I type in company name that exists in the table, I get a
Run-time error '3265': Item not found in this collection. I am not sure why
this is occurring. Below is my completed code:

Dim strWhere As String
Dim strTable As String
Dim strSQL As String
Dim lngLen As Long

If Me.Sourcetable.Value >= 1 Then

strTable = Me.Sourcetable.Value

'Filtering each text box for values
If Not IsNull(Me.txtCompName) Then
strWhere = strWhere & "([Company] like ""*" & Me.txtCompName &
"*"") AND "
End If

If Not IsNull(Me.txtWeb) Then
strWhere = strWhere & "([Web] like ""*" & Me.txtWeb & "*"") AND "
End If

If Not IsNull(Me.txtCity) Then
strWhere = strWhere & "([City] like ""*" & Me.txtCity & "*"")
AND "
End If

If Not IsNull(Me.cboState) Then
strWhere = strWhere & "([State]= """ & Me.cboState & """) AND "
End If

'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'There was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'There is something there, so remove the " AND "
at the end.
strWhere = Left$(strWhere, lngLen)
End If

strSQL = "SELECT * FROM " & strTable & " WHERE " & strWhere & ";"
CurrentDb.QueryDefs("Query1").SQL = strSQL

Else
'Check to see if choice is selected
IsNull (Me.Sourcetable) Or Me.Sourcetable = ""
MsgBox "Please select a source form", vbOKOnly, "Required Data"
Me.Sourcetable.SetFocus
End If

I do see now that I need to add coding to bring up a window if no matches to
the search are found.
Allen Browne said:
The WHERE clause needs AND between the various phrases.

The most flexible way to do that is to tack AND on the end of each one, and
chop off the trailing one at the end.

Dim lngLen as Long
If Not IsNull(Me.txtCompName) Then
strWhere = strWhere & "([Company] like ""*" & Me.txtCompName & "*"")
AND "
End If
If Not IsNull(Me.txtWeb) Then
strWhere = strWhere & "([Web] like ""*" & Me.txtWeb & "*"") AND "
End If
'etc
lngLen = Len(strWhere) - 5 'Without the trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

Once you get this WHERE clause, you can apply it as the Filter of a form
like this:
Forms!Form1.Filter = strWhere
Forms!Form1.FilterOn = True
Or you can open a report like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
Or, if you want to build the whole SQL statement and apply it to Query1:
Dim strSql
strSql = "SELECT * FROM Table1 WHERE " & strWhere & ";"
CurrentDb.QueryDefs("Query1").SQL = strSql

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

angogwanon said:
I am trying to setup a Search Button with four possible variables. The
results need to show all records containing the variables entered.

I have four different tables in my database and a main form that allows
the
person to add new records or search the four queries. The person selects
the
query the wish to search, then types in the search information. I made
sure
the names of the search fields are the same in all four queries (company,
web, city, and state). The state field is a combo box.

I am able to set up the code to get the WHERE part of the query filter.
However, I am unable to figure out how to execute the query with the WHERE
filter. Below is the code I have so far:

Dim strWhere As String
Dim strQuery As String

If Me.Sourcequery.Value >= 1 Then

strQuery = Me.Sourcequery.Value

'Filtering each text box for values
If Not IsNull(Me.txtCompName) Then
strWhere = strWhere & "([Company] like ""*" & Me.txtCompName &
"*"")"
End If

If Not IsNull(Me.txtWeb) Then
strWhere = strWhere & "([Web] like ""*" & Me.txtWeb & "*"")"
End If

If Not IsNull(Me.txtCity) Then
strWhere = strWhere & "([City] like ""*" & Me.txtCity & "*"")"
End If

If Not IsNull(Me.cboState) Then
strWhere = strWhere & "([State]= """ & Me.cboState & """)"
End If

What is the code to open the query with the completed strWhere as the
filter? I appreciate any help I can get. If there is another way, please
let me know. While I have used VB in the past, it has been awhile.
Click to expand...
Click to expand...
 
A

Allen Browne

Did you replace "Query1" with the name of your query?

If the query name is correct, to debug your SQL statement, immediately after
the line:
strSQL = "SELECT * FROM " & strTable & " WHERE " & strWhere & ";"
add this line:
Debug.Print strSql

When it fails, press Ctrl+G to open the Immediate Window. Copy the SQL
statement that printed there, and paste it into SQL View in a query. You can
then see what's wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

angogwanon said:
Thank you very much for answering my question.

Everything works until I get to the last statement:
CurrentDb.QueryDefs("Query1").SQL = strSql

However, when I type in company name that exists in the table, I get a
Run-time error '3265': Item not found in this collection. I am not sure
why
this is occurring. Below is my completed code:

Dim strWhere As String
Dim strTable As String
Dim strSQL As String
Dim lngLen As Long

If Me.Sourcetable.Value >= 1 Then

strTable = Me.Sourcetable.Value

'Filtering each text box for values
If Not IsNull(Me.txtCompName) Then
strWhere = strWhere & "([Company] like ""*" & Me.txtCompName &
"*"") AND "
End If

If Not IsNull(Me.txtWeb) Then
strWhere = strWhere & "([Web] like ""*" & Me.txtWeb & "*"") AND
"
End If

If Not IsNull(Me.txtCity) Then
strWhere = strWhere & "([City] like ""*" & Me.txtCity & "*"")
AND "
End If

If Not IsNull(Me.cboState) Then
strWhere = strWhere & "([State]= """ & Me.cboState & """) AND "
End If

'See if the string has more than 5 characters (a trailng " AND ")
to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'There was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'There is something there, so remove the " AND
"
at the end.
strWhere = Left$(strWhere, lngLen)
End If

strSQL = "SELECT * FROM " & strTable & " WHERE " & strWhere & ";"
CurrentDb.QueryDefs("Query1").SQL = strSQL

Else
'Check to see if choice is selected
IsNull (Me.Sourcetable) Or Me.Sourcetable = ""
MsgBox "Please select a source form", vbOKOnly, "Required Data"
Me.Sourcetable.SetFocus
End If

I do see now that I need to add coding to bring up a window if no matches
to
the search are found.
Allen Browne said:
The WHERE clause needs AND between the various phrases.

The most flexible way to do that is to tack AND on the end of each one,
and
chop off the trailing one at the end.

Dim lngLen as Long
If Not IsNull(Me.txtCompName) Then
strWhere = strWhere & "([Company] like ""*" & Me.txtCompName &
"*"")
AND "
End If
If Not IsNull(Me.txtWeb) Then
strWhere = strWhere & "([Web] like ""*" & Me.txtWeb & "*"") AND "
End If
'etc
lngLen = Len(strWhere) - 5 'Without the trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

Once you get this WHERE clause, you can apply it as the Filter of a form
like this:
Forms!Form1.Filter = strWhere
Forms!Form1.FilterOn = True
Or you can open a report like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
Or, if you want to build the whole SQL statement and apply it to Query1:
Dim strSql
strSql = "SELECT * FROM Table1 WHERE " & strWhere & ";"
CurrentDb.QueryDefs("Query1").SQL = strSql

angogwanon said:
I am trying to setup a Search Button with four possible variables. The
results need to show all records containing the variables entered.

I have four different tables in my database and a main form that allows
the
person to add new records or search the four queries. The person
selects
the
query the wish to search, then types in the search information. I made
sure
the names of the search fields are the same in all four queries
(company,
web, city, and state). The state field is a combo box.

I am able to set up the code to get the WHERE part of the query filter.
However, I am unable to figure out how to execute the query with the
WHERE
filter. Below is the code I have so far:

Dim strWhere As String
Dim strQuery As String

If Me.Sourcequery.Value >= 1 Then

strQuery = Me.Sourcequery.Value

'Filtering each text box for values
If Not IsNull(Me.txtCompName) Then
strWhere = strWhere & "([Company] like ""*" & Me.txtCompName
&
"*"")"
End If

If Not IsNull(Me.txtWeb) Then
strWhere = strWhere & "([Web] like ""*" & Me.txtWeb & "*"")"
End If

If Not IsNull(Me.txtCity) Then
strWhere = strWhere & "([City] like ""*" & Me.txtCity &
"*"")"
End If

If Not IsNull(Me.cboState) Then
strWhere = strWhere & "([State]= """ & Me.cboState & """)"
End If

What is the code to open the query with the completed strWhere as the
filter? I appreciate any help I can get. If there is another way,
please
let me know. While I have used VB in the past, it has been awhile.
Click to expand...
Click to expand...
Click to expand...
 
A

angogwanon

I have four queries depending on which table you chose to search. Therefore,
I added the following code:

If strTable = "College & Association Sources" Then
strQuery = "College & Association Source Query"
End If

If strTable = "Job Posting Sources" Then
strQuery = "Job_Posting_Source_Query"
End If

If strTable = "Professional Sources" Then
strQuery = "Professional Source Query"
End If

If strTable = "Vendor Sources" Then
strQuery = "Vendor Source Query"
End If

'Open query
strSQL = "SELECT * FROM [" & strTable & "] WHERE " & strWhere & ";"
Debug.Print strQuery
Debug.Print strSQL
CurrentDb.QueryDefs("strQuery").SQL = strSQL

However, I still get Run-time error '3265': Item not found in this
collection. I copied the SQL statement as you mentioned below and the
results of the search showed in the query. I have no idea what I am doing
wrong. Both the strQuery and the strSQL are showing the correct value.

Allen Browne said:
Did you replace "Query1" with the name of your query?

If the query name is correct, to debug your SQL statement, immediately after
the line:
strSQL = "SELECT * FROM " & strTable & " WHERE " & strWhere & ";"
add this line:
Debug.Print strSql

When it fails, press Ctrl+G to open the Immediate Window. Copy the SQL
statement that printed there, and paste it into SQL View in a query. You can
then see what's wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

angogwanon said:
Thank you very much for answering my question.

Everything works until I get to the last statement:
CurrentDb.QueryDefs("Query1").SQL = strSql

However, when I type in company name that exists in the table, I get a
Run-time error '3265': Item not found in this collection. I am not sure
why
this is occurring. Below is my completed code:

Dim strWhere As String
Dim strTable As String
Dim strSQL As String
Dim lngLen As Long

If Me.Sourcetable.Value >= 1 Then

strTable = Me.Sourcetable.Value

'Filtering each text box for values
If Not IsNull(Me.txtCompName) Then
strWhere = strWhere & "([Company] like ""*" & Me.txtCompName &
"*"") AND "
End If

If Not IsNull(Me.txtWeb) Then
strWhere = strWhere & "([Web] like ""*" & Me.txtWeb & "*"") AND
"
End If

If Not IsNull(Me.txtCity) Then
strWhere = strWhere & "([City] like ""*" & Me.txtCity & "*"")
AND "
End If

If Not IsNull(Me.cboState) Then
strWhere = strWhere & "([State]= """ & Me.cboState & """) AND "
End If

'See if the string has more than 5 characters (a trailng " AND ")
to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'There was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'There is something there, so remove the " AND
"
at the end.
strWhere = Left$(strWhere, lngLen)
End If

strSQL = "SELECT * FROM " & strTable & " WHERE " & strWhere & ";"
CurrentDb.QueryDefs("Query1").SQL = strSQL

Else
'Check to see if choice is selected
IsNull (Me.Sourcetable) Or Me.Sourcetable = ""
MsgBox "Please select a source form", vbOKOnly, "Required Data"
Me.Sourcetable.SetFocus
End If

I do see now that I need to add coding to bring up a window if no matches
to
the search are found.
Allen Browne said:
The WHERE clause needs AND between the various phrases.

The most flexible way to do that is to tack AND on the end of each one,
and
chop off the trailing one at the end.

Dim lngLen as Long
If Not IsNull(Me.txtCompName) Then
strWhere = strWhere & "([Company] like ""*" & Me.txtCompName &
"*"")
AND "
End If
If Not IsNull(Me.txtWeb) Then
strWhere = strWhere & "([Web] like ""*" & Me.txtWeb & "*"") AND "
End If
'etc
lngLen = Len(strWhere) - 5 'Without the trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

Once you get this WHERE clause, you can apply it as the Filter of a form
like this:
Forms!Form1.Filter = strWhere
Forms!Form1.FilterOn = True
Or you can open a report like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
Or, if you want to build the whole SQL statement and apply it to Query1:
Dim strSql
strSql = "SELECT * FROM Table1 WHERE " & strWhere & ";"
CurrentDb.QueryDefs("Query1").SQL = strSql

I am trying to setup a Search Button with four possible variables. The
results need to show all records containing the variables entered.

I have four different tables in my database and a main form that allows
the
person to add new records or search the four queries. The person
selects
the
query the wish to search, then types in the search information. I made
sure
the names of the search fields are the same in all four queries
(company,
web, city, and state). The state field is a combo box.

I am able to set up the code to get the WHERE part of the query filter.
However, I am unable to figure out how to execute the query with the
WHERE
filter. Below is the code I have so far:

Dim strWhere As String
Dim strQuery As String

If Me.Sourcequery.Value >= 1 Then

strQuery = Me.Sourcequery.Value

'Filtering each text box for values
If Not IsNull(Me.txtCompName) Then
strWhere = strWhere & "([Company] like ""*" & Me.txtCompName
&
"*"")"
End If

If Not IsNull(Me.txtWeb) Then
strWhere = strWhere & "([Web] like ""*" & Me.txtWeb & "*"")"
End If

If Not IsNull(Me.txtCity) Then
strWhere = strWhere & "([City] like ""*" & Me.txtCity &
"*"")"
End If

If Not IsNull(Me.cboState) Then
strWhere = strWhere & "([State]= """ & Me.cboState & """)"
End If

What is the code to open the query with the completed strWhere as the
filter? I appreciate any help I can get. If there is another way,
please
let me know. While I have used VB in the past, it has been awhile.
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

strQuery is the name of a variable (not the name of a query), so drop the
quotes:
CurrentDb.QueryDefs(strQuery).SQL = strSQL

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

angogwanon said:
I have four queries depending on which table you chose to search.
Therefore,
I added the following code:

If strTable = "College & Association Sources" Then
strQuery = "College & Association Source Query"
End If

If strTable = "Job Posting Sources" Then
strQuery = "Job_Posting_Source_Query"
End If

If strTable = "Professional Sources" Then
strQuery = "Professional Source Query"
End If

If strTable = "Vendor Sources" Then
strQuery = "Vendor Source Query"
End If

'Open query
strSQL = "SELECT * FROM [" & strTable & "] WHERE " & strWhere & ";"
Debug.Print strQuery
Debug.Print strSQL
CurrentDb.QueryDefs("strQuery").SQL = strSQL

However, I still get Run-time error '3265': Item not found in this
collection. I copied the SQL statement as you mentioned below and the
results of the search showed in the query. I have no idea what I am doing
wrong. Both the strQuery and the strSQL are showing the correct value.

Allen Browne said:
Did you replace "Query1" with the name of your query?

If the query name is correct, to debug your SQL statement, immediately
after
the line:
strSQL = "SELECT * FROM " & strTable & " WHERE " & strWhere & ";"
add this line:
Debug.Print strSql

When it fails, press Ctrl+G to open the Immediate Window. Copy the SQL
statement that printed there, and paste it into SQL View in a query. You
can
then see what's wrong.

angogwanon said:
Thank you very much for answering my question.

Everything works until I get to the last statement:
CurrentDb.QueryDefs("Query1").SQL = strSql

However, when I type in company name that exists in the table, I get a
Run-time error '3265': Item not found in this collection. I am not
sure
why
this is occurring. Below is my completed code:

Dim strWhere As String
Dim strTable As String
Dim strSQL As String
Dim lngLen As Long

If Me.Sourcetable.Value >= 1 Then

strTable = Me.Sourcetable.Value

'Filtering each text box for values
If Not IsNull(Me.txtCompName) Then
strWhere = strWhere & "([Company] like ""*" & Me.txtCompName
&
"*"") AND "
End If

If Not IsNull(Me.txtWeb) Then
strWhere = strWhere & "([Web] like ""*" & Me.txtWeb & "*"")
AND
"
End If

If Not IsNull(Me.txtCity) Then
strWhere = strWhere & "([City] like ""*" & Me.txtCity &
"*"")
AND "
End If

If Not IsNull(Me.cboState) Then
strWhere = strWhere & "([State]= """ & Me.cboState & """)
AND "
End If

'See if the string has more than 5 characters (a trailng " AND
")
to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'There was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'There is something there, so remove the "
AND
"
at the end.
strWhere = Left$(strWhere, lngLen)
End If

strSQL = "SELECT * FROM " & strTable & " WHERE " & strWhere & ";"
CurrentDb.QueryDefs("Query1").SQL = strSQL

Else
'Check to see if choice is selected
IsNull (Me.Sourcetable) Or Me.Sourcetable = ""
MsgBox "Please select a source form", vbOKOnly, "Required Data"
Me.Sourcetable.SetFocus
End If

I do see now that I need to add coding to bring up a window if no
matches
to
the search are found.
:

The WHERE clause needs AND between the various phrases.

The most flexible way to do that is to tack AND on the end of each
one,
and
chop off the trailing one at the end.

Dim lngLen as Long
If Not IsNull(Me.txtCompName) Then
strWhere = strWhere & "([Company] like ""*" & Me.txtCompName &
"*"")
AND "
End If
If Not IsNull(Me.txtWeb) Then
strWhere = strWhere & "([Web] like ""*" & Me.txtWeb & "*"")
AND "
End If
'etc
lngLen = Len(strWhere) - 5 'Without the trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

Once you get this WHERE clause, you can apply it as the Filter of a
form
like this:
Forms!Form1.Filter = strWhere
Forms!Form1.FilterOn = True
Or you can open a report like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
Or, if you want to build the whole SQL statement and apply it to
Query1:
Dim strSql
strSql = "SELECT * FROM Table1 WHERE " & strWhere & ";"
CurrentDb.QueryDefs("Query1").SQL = strSql

I am trying to setup a Search Button with four possible variables.
The
results need to show all records containing the variables entered.

I have four different tables in my database and a main form that
allows
the
person to add new records or search the four queries. The person
selects
the
query the wish to search, then types in the search information. I
made
sure
the names of the search fields are the same in all four queries
(company,
web, city, and state). The state field is a combo box.

I am able to set up the code to get the WHERE part of the query
filter.
However, I am unable to figure out how to execute the query with the
WHERE
filter. Below is the code I have so far:

Dim strWhere As String
Dim strQuery As String

If Me.Sourcequery.Value >= 1 Then

strQuery = Me.Sourcequery.Value

'Filtering each text box for values
If Not IsNull(Me.txtCompName) Then
strWhere = strWhere & "([Company] like ""*" &
Me.txtCompName
&
"*"")"
End If

If Not IsNull(Me.txtWeb) Then
strWhere = strWhere & "([Web] like ""*" & Me.txtWeb &
"*"")"
End If

If Not IsNull(Me.txtCity) Then
strWhere = strWhere & "([City] like ""*" & Me.txtCity &
"*"")"
End If

If Not IsNull(Me.cboState) Then
strWhere = strWhere & "([State]= """ & Me.cboState &
""")"
End If

What is the code to open the query with the completed strWhere as
the
filter? I appreciate any help I can get. If there is another way,
please
let me know. While I have used VB in the past, it has been awhile.
Click to expand...
Click to expand...
Click to expand...
 
A

angogwanon

Thank you for all your help. I got the Search Button to work after adding
the line doCmd.openquery strQuery at the end.

I would not have got this Search Button working without your help. Thanks
again!!!!!

Allen Browne said:
strQuery is the name of a variable (not the name of a query), so drop the
quotes:
CurrentDb.QueryDefs(strQuery).SQL = strSQL

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

angogwanon said:
I have four queries depending on which table you chose to search.
Therefore,
I added the following code:

If strTable = "College & Association Sources" Then
strQuery = "College & Association Source Query"
End If

If strTable = "Job Posting Sources" Then
strQuery = "Job_Posting_Source_Query"
End If

If strTable = "Professional Sources" Then
strQuery = "Professional Source Query"
End If

If strTable = "Vendor Sources" Then
strQuery = "Vendor Source Query"
End If

'Open query
strSQL = "SELECT * FROM [" & strTable & "] WHERE " & strWhere & ";"
Debug.Print strQuery
Debug.Print strSQL
CurrentDb.QueryDefs("strQuery").SQL = strSQL

However, I still get Run-time error '3265': Item not found in this
collection. I copied the SQL statement as you mentioned below and the
results of the search showed in the query. I have no idea what I am doing
wrong. Both the strQuery and the strSQL are showing the correct value.

Allen Browne said:
Did you replace "Query1" with the name of your query?

If the query name is correct, to debug your SQL statement, immediately
after
the line:
strSQL = "SELECT * FROM " & strTable & " WHERE " & strWhere & ";"
add this line:
Debug.Print strSql

When it fails, press Ctrl+G to open the Immediate Window. Copy the SQL
statement that printed there, and paste it into SQL View in a query. You
can
then see what's wrong.

Thank you very much for answering my question.

Everything works until I get to the last statement:
CurrentDb.QueryDefs("Query1").SQL = strSql

However, when I type in company name that exists in the table, I get a
Run-time error '3265': Item not found in this collection. I am not
sure
why
this is occurring. Below is my completed code:

Dim strWhere As String
Dim strTable As String
Dim strSQL As String
Dim lngLen As Long

If Me.Sourcetable.Value >= 1 Then

strTable = Me.Sourcetable.Value

'Filtering each text box for values
If Not IsNull(Me.txtCompName) Then
strWhere = strWhere & "([Company] like ""*" & Me.txtCompName
&
"*"") AND "
End If

If Not IsNull(Me.txtWeb) Then
strWhere = strWhere & "([Web] like ""*" & Me.txtWeb & "*"")
AND
"
End If

If Not IsNull(Me.txtCity) Then
strWhere = strWhere & "([City] like ""*" & Me.txtCity &
"*"")
AND "
End If

If Not IsNull(Me.cboState) Then
strWhere = strWhere & "([State]= """ & Me.cboState & """)
AND "
End If

'See if the string has more than 5 characters (a trailng " AND
")
to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'There was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'There is something there, so remove the "
AND
"
at the end.
strWhere = Left$(strWhere, lngLen)
End If

strSQL = "SELECT * FROM " & strTable & " WHERE " & strWhere & ";"
CurrentDb.QueryDefs("Query1").SQL = strSQL

Else
'Check to see if choice is selected
IsNull (Me.Sourcetable) Or Me.Sourcetable = ""
MsgBox "Please select a source form", vbOKOnly, "Required Data"
Me.Sourcetable.SetFocus
End If

I do see now that I need to add coding to bring up a window if no
matches
to
the search are found.
:

The WHERE clause needs AND between the various phrases.

The most flexible way to do that is to tack AND on the end of each
one,
and
chop off the trailing one at the end.

Dim lngLen as Long
If Not IsNull(Me.txtCompName) Then
strWhere = strWhere & "([Company] like ""*" & Me.txtCompName &
"*"")
AND "
End If
If Not IsNull(Me.txtWeb) Then
strWhere = strWhere & "([Web] like ""*" & Me.txtWeb & "*"")
AND "
End If
'etc
lngLen = Len(strWhere) - 5 'Without the trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

Once you get this WHERE clause, you can apply it as the Filter of a
form
like this:
Forms!Form1.Filter = strWhere
Forms!Form1.FilterOn = True
Or you can open a report like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
Or, if you want to build the whole SQL statement and apply it to
Query1:
Dim strSql
strSql = "SELECT * FROM Table1 WHERE " & strWhere & ";"
CurrentDb.QueryDefs("Query1").SQL = strSql

I am trying to setup a Search Button with four possible variables.
The
results need to show all records containing the variables entered.

I have four different tables in my database and a main form that
allows
the
person to add new records or search the four queries. The person
selects
the
query the wish to search, then types in the search information. I
made
sure
the names of the search fields are the same in all four queries
(company,
web, city, and state). The state field is a combo box.

I am able to set up the code to get the WHERE part of the query
filter.
However, I am unable to figure out how to execute the query with the
WHERE
filter. Below is the code I have so far:

Dim strWhere As String
Dim strQuery As String

If Me.Sourcequery.Value >= 1 Then

strQuery = Me.Sourcequery.Value

'Filtering each text box for values
If Not IsNull(Me.txtCompName) Then
strWhere = strWhere & "([Company] like ""*" &
Me.txtCompName
&
"*"")"
End If

If Not IsNull(Me.txtWeb) Then
strWhere = strWhere & "([Web] like ""*" & Me.txtWeb &
"*"")"
End If

If Not IsNull(Me.txtCity) Then
strWhere = strWhere & "([City] like ""*" & Me.txtCity &
"*"")"
End If

If Not IsNull(Me.cboState) Then
strWhere = strWhere & "([State]= """ & Me.cboState &
""")"
End If

What is the code to open the query with the completed strWhere as
the
filter? I appreciate any help I can get. If there is another way,
please
let me know. While I have used VB in the past, it has been awhile.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter for years and keywords 6
Search Form with a SubForm 3
Problem with Search Button and SQL Statement 4
Require a field in search form 3
runtime error 3075 2
Allen Brownes ECount() 5
Error on modified Allenbrowne SearchForm 6
Compile error: Argument not optional 3

Top