By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 1,920 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

"Like" in Query String

P: n/a
Hello All!

I am using the following code during an On Click event for a button.
It works properly to create a query for a report:

Private Sub Command14_Click()
If IsNull(Me.Text30) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text30.Value & "'"
End If
If IsNull(Me.Text31) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text31.Value & "'"
End If
If IsNull(Me.Text32) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text32.Value & "'"
End If
If IsNull(Me.Text33) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text33.Value & "'"
End If

If IsNull(Me.Text34) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text34.Value & "'"
End If

If IsNull(Me.Text35) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text35.Value & "'"
End If

If IsNull(Me.Text36) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text36.Value & "'"
End If

If IsNull(Me.Text37) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text37.Value & "'"
End If
If Len(strWhere) 1 Then

strWhere = Mid$(strWhere, 2)
strWhere = "AC IN (" & strWhere & ")"
'strWhere = "" & strLike & " AC IN (" & strWhere & ")"
End If

DoCmd.OpenReport "qryAm", acViewPreview, , strWhere
End Sub

----------------------------------

The line I would specifically like to ask about is the following:

strWhere = "AC IN (" & strWhere & ")"

How would I include a "Like" in the string?
Psuedocode - AC IN LIKE *strWhwere*

Thank you in advance!

Brad

Nov 30 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Drum2001 wrote:
Hello All!

I am using the following code during an On Click event for a button.
It works properly to create a query for a report:

Private Sub Command14_Click()
If IsNull(Me.Text30) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text30.Value & "'"
End If
If IsNull(Me.Text31) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text31.Value & "'"
End If
If IsNull(Me.Text32) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text32.Value & "'"
End If
If IsNull(Me.Text33) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text33.Value & "'"
End If

If IsNull(Me.Text34) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text34.Value & "'"
End If

If IsNull(Me.Text35) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text35.Value & "'"
End If

If IsNull(Me.Text36) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text36.Value & "'"
End If

If IsNull(Me.Text37) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text37.Value & "'"
End If
If Len(strWhere) 1 Then

strWhere = Mid$(strWhere, 2)
strWhere = "AC IN (" & strWhere & ")"
'strWhere = "" & strLike & " AC IN (" & strWhere & ")"
End If

DoCmd.OpenReport "qryAm", acViewPreview, , strWhere
End Sub

----------------------------------

The line I would specifically like to ask about is the following:

strWhere = "AC IN (" & strWhere & ")"

How would I include a "Like" in the string?
Psuedocode - AC IN LIKE *strWhwere*

Thank you in advance!

Brad
Why not make your query use ORs instead
StrWhere = AC Like "Test*" Or
AC Like "Question*" Or
AC Like "Answer*"

Nov 30 '06 #2

P: n/a
Would that be static?

Users are not always going to enter information in every textbox.

If not, how would I exactly code the strWhere?
salad wrote:
Drum2001 wrote:
Hello All!

I am using the following code during an On Click event for a button.
It works properly to create a query for a report:

Private Sub Command14_Click()
If IsNull(Me.Text30) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text30.Value & "'"
End If
If IsNull(Me.Text31) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text31.Value & "'"
End If
If IsNull(Me.Text32) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text32.Value & "'"
End If
If IsNull(Me.Text33) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text33.Value & "'"
End If

If IsNull(Me.Text34) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text34.Value & "'"
End If

If IsNull(Me.Text35) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text35.Value & "'"
End If

If IsNull(Me.Text36) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text36.Value & "'"
End If

If IsNull(Me.Text37) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text37.Value & "'"
End If
If Len(strWhere) 1 Then

strWhere = Mid$(strWhere, 2)
strWhere = "AC IN (" & strWhere & ")"
'strWhere = "" & strLike & " AC IN (" & strWhere & ")"
End If

DoCmd.OpenReport "qryAm", acViewPreview, , strWhere
End Sub

----------------------------------

The line I would specifically like to ask about is the following:

strWhere = "AC IN (" & strWhere & ")"

How would I include a "Like" in the string?
Psuedocode - AC IN LIKE *strWhwere*

Thank you in advance!

Brad
Why not make your query use ORs instead
StrWhere = AC Like "Test*" Or
AC Like "Question*" Or
AC Like "Answer*"
Nov 30 '06 #3

P: n/a
Drum2001 wrote:
Would that be static?

Users are not always going to enter information in every textbox.

If not, how would I exactly code the strWhere?
salad wrote:
>>Drum2001 wrote:

>>>Hello All!

I am using the following code during an On Click event for a button.
It works properly to create a query for a report:

Private Sub Command14_Click()
If IsNull(Me.Text30) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text30.Value & "'"
End If
If IsNull(Me.Text31) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text31.Value & "'"
End If
If IsNull(Me.Text32) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text32.Value & "'"
End If
If IsNull(Me.Text33) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text33.Value & "'"
End If

If IsNull(Me.Text34) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text34.Value & "'"
End If

If IsNull(Me.Text35) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text35.Value & "'"
End If

If IsNull(Me.Text36) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text36.Value & "'"
End If

If IsNull(Me.Text37) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text37.Value & "'"
End If
If Len(strWhere) 1 Then

strWhere = Mid$(strWhere, 2)
strWhere = "AC IN (" & strWhere & ")"
'strWhere = "" & strLike & " AC IN (" & strWhere & ")"
End If

DoCmd.OpenReport "qryAm", acViewPreview, , strWhere
End Sub

----------------------------------

The line I would specifically like to ask about is the following:

strWhere = "AC IN (" & strWhere & ")"

How would I include a "Like" in the string?
Psuedocode - AC IN LIKE *strWhwere*

Thank you in advance!

Brad

Why not make your query use ORs instead
StrWhere = AC Like "Test*" Or
AC Like "Question*" Or
AC Like "Answer*"

Looking at the code you have up above you do check 3 or 4 checkboxes.
If Not IsNull(x) Then
strWhere = "AC Like '" & x & "' Or "
ENdif
If Not IsNull(y) Then
strWhere = "AC Like '" & y & "' Or "
ENdif
'remove the OR
strWhere = Left(strWhere,Len(strWHere)-4)
Nov 30 '06 #4

P: n/a
When I use this code, it only searches one textbox. It is the one that
it uses last.. Seems like its overwrighting. This is what I have:

------------------------------------------------------------------------------------------------
Private Sub Command14_Click()
If Not IsNull(Me.Text30) Then
strWHere = "ACLike '" & Me.Text30 & "*" & "' Or "
End If

If Not IsNull(Me.Text31) Then
strWHere = "ACLike '" & Me.Text31 & "*" & "' Or "
End If

If Not IsNull(Me.Text32) Then
strWHere = "AC Like '" & Me.Text32 & "*" & "' Or "
End If

If Not IsNull(Me.Text33) Then
strWHere = "ACLike '" & Me.Text33 & "*" & "' Or "
End If

If Not IsNull(Me.Text34) Then
strWHere = "AC Like '" & Me.Text34 & "*" & "' Or "
End If

If Not IsNull(Me.Text35) Then
strWHere = "AC Like '" & Me.Text35 & "*" & "' Or "
End If

If Not IsNull(Me.Text36) Then
strWHere = "AC Like '" & Me.Text36 & "*" & "' Or "
End If

If Not IsNull(Me.Text37) Then
strWHere = "AC Like '" & Me.Text37 & "'"
End If
'remove the OR
strWHere = Left(strWHere, Len(strWHere) - 4)
DoCmd.OpenReport "qryAcronym", acViewPreview, , strWHere

End Sub

------------------------------------------------------------------------------------------------

Any suggestions? I really appreciate your assistance!

Brad
salad wrote:
Drum2001 wrote:
Would that be static?

Users are not always going to enter information in every textbox.

If not, how would I exactly code the strWhere?
salad wrote:
>Drum2001 wrote:
Hello All!

I am using the following code during an On Click event for a button.
It works properly to create a query for a report:

Private Sub Command14_Click()
If IsNull(Me.Text30) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text30.Value & "'"
End If
If IsNull(Me.Text31) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text31.Value & "'"
End If
If IsNull(Me.Text32) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text32.Value & "'"
End If
If IsNull(Me.Text33) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text33.Value & "'"
End If

If IsNull(Me.Text34) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text34.Value & "'"
End If

If IsNull(Me.Text35) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text35.Value & "'"
End If

If IsNull(Me.Text36) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text36.Value & "'"
End If

If IsNull(Me.Text37) Then
' No criteria here - so ignore
Else
strWhere = strWhere & ",'" & Me.Text37.Value & "'"
End If
If Len(strWhere) 1 Then

strWhere = Mid$(strWhere, 2)
strWhere = "AC IN (" & strWhere & ")"
'strWhere = "" & strLike & " AC IN (" & strWhere & ")"
End If

DoCmd.OpenReport "qryAm", acViewPreview, , strWhere
End Sub

----------------------------------

The line I would specifically like to ask about is the following:

strWhere = "AC IN (" & strWhere & ")"

How would I include a "Like" in the string?
Psuedocode - AC IN LIKE *strWhwere*

Thank you in advance!

Brad
Why not make your query use ORs instead
StrWhere = AC Like "Test*" Or
AC Like "Question*" Or
AC Like "Answer*"
Looking at the code you have up above you do check 3 or 4 checkboxes.
If Not IsNull(x) Then
strWhere = "AC Like '" & x & "' Or "
ENdif
If Not IsNull(y) Then
strWhere = "AC Like '" & y & "' Or "
ENdif
'remove the OR
strWhere = Left(strWhere,Len(strWHere)-4)
Dec 1 '06 #5

P: n/a

Drum2001 wrote:
When I use this code, it only searches one textbox. It is the one that
it uses last.. Seems like its overwrighting. This is what I have:

------------------------------------------------------------------------------------------------
Private Sub Command14_Click()
If Not IsNull(Me.Text30) Then
strWHere = "ACLike '" & Me.Text30 & "*" & "' Or "
End If

If Not IsNull(Me.Text31) Then
strWHere = "ACLike '" & Me.Text31 & "*" & "' Or "
End If

If Not IsNull(Me.Text32) Then
strWHere = "AC Like '" & Me.Text32 & "*" & "' Or "
End If

If Not IsNull(Me.Text33) Then
strWHere = "ACLike '" & Me.Text33 & "*" & "' Or "
End If

If Not IsNull(Me.Text34) Then
strWHere = "AC Like '" & Me.Text34 & "*" & "' Or "
End If

If Not IsNull(Me.Text35) Then
strWHere = "AC Like '" & Me.Text35 & "*" & "' Or "
End If

If Not IsNull(Me.Text36) Then
strWHere = "AC Like '" & Me.Text36 & "*" & "' Or "
End If

If Not IsNull(Me.Text37) Then
strWHere = "AC Like '" & Me.Text37 & "'"
End If
'remove the OR
strWHere = Left(strWHere, Len(strWHere) - 4)
DoCmd.OpenReport "qryAcronym", acViewPreview, , strWHere

End Sub

------------------------------------------------------------------------------------------------

Any suggestions? I really appreciate your assistance!

Brad
You need to replace "strWHere = " with "strWHere = strWhere & " in your
code.

Bruce

Dec 1 '06 #6

P: n/a
Drum2001 wrote:
When I use this code, it only searches one textbox. It is the one that
it uses last.. Seems like its overwrighting. This is what I have:

------------------------------------------------------------------------------------------------
Private Sub Command14_Click()
If Not IsNull(Me.Text30) Then
strWHere = "ACLike '" & Me.Text30 & "*" & "' Or "
End If

If Not IsNull(Me.Text31) Then
strWHere = "ACLike '" & Me.Text31 & "*" & "' Or "
End If
<snip>

First of all your form object naming convention, or lack of one sucks. 8)

But to address your problem, your code is rewriting the whole of
strWhere everytime an if statement is executed. To fix what you have,
on the second if statement shown above, and all the remaining ones replace:

strWHere = "ACLike '" & Me.Text31 & "*" & "' Or "

with

strWHere = strwhere & "ACLike '" & Me.Text31 & "*" & "' Or "

That will do it.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
Dec 1 '06 #7

P: n/a
Tim Marshall wrote:
Drum2001 wrote:
>When I use this code, it only searches one textbox. It is the one that
it uses last.. Seems like its overwrighting. This is what I have:

------------------------------------------------------------------------------------------------

Private Sub Command14_Click()
If Not IsNull(Me.Text30) Then
strWHere = "ACLike '" & Me.Text30 & "*" & "' Or "
End If

If Not IsNull(Me.Text31) Then
strWHere = "ACLike '" & Me.Text31 & "*" & "' Or "
End If


<snip>

First of all your form object naming convention, or lack of one sucks. 8)
I would be constantly wondering what Textxx and Textyy were. Debugging
time would be high in a serious application.
But to address your problem, your code is rewriting the whole of
strWhere everytime an if statement is executed. To fix what you have,
on the second if statement shown above, and all the remaining ones replace:

strWHere = "ACLike '" & Me.Text31 & "*" & "' Or "

with

strWHere = strwhere & "ACLike '" & Me.Text31 & "*" & "' Or "

That will do it.
DOH! Thanks Tim and Deluxe for spotting the logic error.
Dec 1 '06 #8

P: n/a
EXCELLENT!!!
THANK YOU ALL FOR YOUR ASSISTANCE!!!
Tim Marshall wrote:
Drum2001 wrote:
When I use this code, it only searches one textbox. It is the one that
it uses last.. Seems like its overwrighting. This is what I have:

------------------------------------------------------------------------------------------------
Private Sub Command14_Click()
If Not IsNull(Me.Text30) Then
strWHere = "ACLike '" & Me.Text30 & "*" & "' Or "
End If

If Not IsNull(Me.Text31) Then
strWHere = "ACLike '" & Me.Text31 & "*" & "' Or "
End If

<snip>

First of all your form object naming convention, or lack of one sucks. 8)

But to address your problem, your code is rewriting the whole of
strWhere everytime an if statement is executed. To fix what you have,
on the second if statement shown above, and all the remaining ones replace:

strWHere = "ACLike '" & Me.Text31 & "*" & "' Or "

with

strWHere = strwhere & "ACLike '" & Me.Text31 & "*" & "' Or "

That will do it.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
Dec 1 '06 #9

P: n/a
salad wrote:
DOH! Thanks Tim and Deluxe for spotting the logic error.
Sorry salad, I read the post and didn't notice the "Re" - I thought it
was a first post. Didn't mean to jump in on you. 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
Dec 1 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.