Complex multiple criteria in one text box field in QBF

[opekemi]

Hope you can all help with this:

I have a form that is attached to a query.

In my query, I have the following criteria:

[Company] Like "*" & [Forms]![QBF_Form]![Sales] & "*" Or
[Forms]![QBF_Form]![Sales] Is Null

But it only allows me to search that particular field either when I put
in a blank or when I put in something like: *cad*

I want to be able to also search like this:
not *cad*
<> *cad*
*cad* or *pdf*

This should be possible, but I have searched all over and can't seem
to get a good answer.

Thanks for your help!

 

[Douglas J. Steele]

If you want all records where the company code doesn't contain whatever text
is in the Sales text box on your form, you can use [Company] Not Like "*" &
[Forms]![QBF_Form]![Sales] & "*", but there's no logical way that you'll be
able to set something up that will all you to either search for a field or
search for the field not existing using the same parameter.

There's no easy way to allow for multiple expressions with wild cards.

 

[opekemi]

Thanks Doug,

Okay, let's forget about the not cad*

I want to be able to search this though:

*cad* or *pdf* or ...

Even if there is no easy way, I would still ike to know if I can
implement it.

Thanks for your help.

Any help is appreciated!

 

[John Spencer]

Without using VBA to build the query (or at least the where clause) this is
almost impossible.

 

[Douglas J. Steele]

You can't do it using a parameter. The only way would be to dynamically
rewrite the SQL of the query.

I'll give a simple example of how to do this. Assuming that my original
query was something like:

SELECT Field1, Field2, Field3
FROM MyTable
WHERE [Company] LIKE "*" & [Forms]![QBF_Form]![Sales] & "*"

and that it's saved as qryMyQuery.

You could do something like the following:

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String
Dim strValue As String
Dim strWhere As String

strSQL = "SELECT Field1, Field2, Field3 " & _
"FROM MyTable "
strWhere = ""

strValue = InputBox("Input a company name. Just hit Enter to stop", _
"Company Name", "")
Do While Len(strValue) > 0
strWhere = strWhere & "[Company] Like '*" & strValue & "*' Or"
strValue = InputBox("Input a company name. Just hit Enter to stop", _
"Company Name", "")
Loop

If Len(strWhere) > 0 Then
strSQL = strSQL & "WHERE " & Left$(strWhere, Len(strWhere) - 3))
End If

Set qdfCurr = CurrentDb.QueryDefs("qryMyQuery")
qdfCurr.SQL = strSQL

 

[opekemi]

Thanks,
I am not sure why I am getting some errors.
Here is what I did:
I copied the code into the command button on my form. This form has
unbound textboxes so that the user can input values that run a the
query behind it.
But when I input value in one of the textboxes (company textbox) on the
form (15 textboxes in the form altogether), and click on the command
button, a parameter box comes up and ask for the values all over again.
When I either input a value or not and then click on ok, I get this
debug error, and the following line in the code is highlighted:
qdfCurr.SQL = strSQL

I don't want a new parameter box to come up since my form has textboxes
to input the values.

Just to clarify.
Company is the name of my field in my table, but the corresponding
textbox name in the form is Sales.
Also, what does the "3" represent in the code?

Thanks so much for your help

 

[Douglas J. Steele]

Post the actual code you ended up with.

If you're asking about the 3 in Left$(strWhere, Len(strWhere) - 3)), it's to
remove the " OR" from the end of the string.

Are you hoping to allow them to type possible names into any of the 15 text
boxes, or are only some of them for company names? What have you named the
15 text boxes?

If you want all 15 to be used for company name, rename them so that they're
something like Text1, Text2, Text3, up to Text15. Then try:

Dim qdfCurr As DAO.QueryDef
Dim intLoop As Integer
Dim strSQL As String
Dim strValue As String
Dim strWhere As String

strSQL = "SELECT Field1, Field2, Field3 " & _
"FROM MyTable "
strWhere = ""

For intLoop = 1 To 15
If Len(Me.Controls("Text" & intLoop) & "") > 0 Then
strWhere = strWhere & "[Company] Like '*" & _
Me.Controls("Text" & intLoop) & "*' Or"
End If
Next intLoop

If Len(strWhere) > 0 Then
strSQL = strSQL & "WHERE " & Left$(strWhere, Len(strWhere) - 3))
End If

Set qdfCurr = CurrentDb.QueryDefs("qryMyQuery")
qdfCurr.SQL = strSQL

 

[opekemi]

Thanks Doug,
The textboxes are all different (actually 13 in all), they don't relate
to company names, they are other fields in the table.
The user could fill in all the textboxes on the form and run the query
or fill none of the textboxes or just fill some of the textboxes.
The names of other textboxes include:
Sales
Code
Number
Corporate
Country
Valid
Exempt
#1
#2
#3
StartDate
EndDate
State

Thanks again!

Post the actual code you ended up with.

If you're asking about the 3 in Left$(strWhere, Len(strWhere) - 3)), it's to
remove the " OR" from the end of the string.

Are you hoping to allow them to type possible names into any of the 15 text
boxes, or are only some of them for company names? What have you named the
15 text boxes?

If you want all 15 to be used for company name, rename them so that they're
something like Text1, Text2, Text3, up to Text15. Then try:

Dim qdfCurr As DAO.QueryDef
Dim intLoop As Integer
Dim strSQL As String
Dim strValue As String
Dim strWhere As String

strSQL = "SELECT Field1, Field2, Field3 " & _
"FROM MyTable "
strWhere = ""

For intLoop = 1 To 15
If Len(Me.Controls("Text" & intLoop) & "") > 0 Then
strWhere = strWhere & "[Company] Like '*" & _
Me.Controls("Text" & intLoop) & "*' Or"
End If
Next intLoop

If Len(strWhere) > 0 Then
strSQL = strSQL & "WHERE " & Left$(strWhere, Len(strWhere) - 3))
End If

Set qdfCurr = CurrentDb.QueryDefs("qryMyQuery")
qdfCurr.SQL = strSQL



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks,
I am not sure why I am getting some errors.
Here is what I did:
I copied the code into the command button on my form. This form has
unbound textboxes so that the user can input values that run a the
query behind it.
But when I input value in one of the textboxes (company textbox) on the
form (15 textboxes in the form altogether), and click on the command
button, a parameter box comes up and ask for the values all over again.
When I either input a value or not and then click on ok, I get this
debug error, and the following line in the code is highlighted:
qdfCurr.SQL = strSQL

I don't want a new parameter box to come up since my form has textboxes
to input the values.

Just to clarify.
Company is the name of my field in my table, but the corresponding
textbox name in the form is Sales.
Also, what does the "3" represent in the code?

Thanks so much for your help

 

[Douglas J. Steele]

I don't see where you allow them to enter multiple possible values for the
Company name.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Doug,
The textboxes are all different (actually 13 in all), they don't relate
to company names, they are other fields in the table.
The user could fill in all the textboxes on the form and run the query
or fill none of the textboxes or just fill some of the textboxes.
The names of other textboxes include:
Sales
Code
Number
Corporate
Country
Valid
Exempt
#1
#2
#3
StartDate
EndDate
State

Thanks again!

Post the actual code you ended up with.

If you're asking about the 3 in Left$(strWhere, Len(strWhere) - 3)), it's
to
remove the " OR" from the end of the string.

Are you hoping to allow them to type possible names into any of the 15
text
boxes, or are only some of them for company names? What have you named
the
15 text boxes?

If you want all 15 to be used for company name, rename them so that
they're
something like Text1, Text2, Text3, up to Text15. Then try:

Dim qdfCurr As DAO.QueryDef
Dim intLoop As Integer
Dim strSQL As String
Dim strValue As String
Dim strWhere As String

strSQL = "SELECT Field1, Field2, Field3 " & _
"FROM MyTable "
strWhere = ""

For intLoop = 1 To 15
If Len(Me.Controls("Text" & intLoop) & "") > 0 Then
strWhere = strWhere & "[Company] Like '*" & _
Me.Controls("Text" & intLoop) & "*' Or"
End If
Next intLoop

If Len(strWhere) > 0 Then
strSQL = strSQL & "WHERE " & Left$(strWhere, Len(strWhere) - 3))
End If

Set qdfCurr = CurrentDb.QueryDefs("qryMyQuery")
qdfCurr.SQL = strSQL



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks,
I am not sure why I am getting some errors.
Here is what I did:
I copied the code into the command button on my form. This form has
unbound textboxes so that the user can input values that run a the
query behind it.
But when I input value in one of the textboxes (company textbox) on the
form (15 textboxes in the form altogether), and click on the command
button, a parameter box comes up and ask for the values all over again.
When I either input a value or not and then click on ok, I get this
debug error, and the following line in the code is highlighted:
qdfCurr.SQL = strSQL

I don't want a new parameter box to come up since my form has textboxes
to input the values.

Just to clarify.
Company is the name of my field in my table, but the corresponding
textbox name in the form is Sales.
Also, what does the "3" represent in the code?

Thanks so much for your help