448,560 Members | 1,355 Online
Need help? Post your question and get tips & solutions from a community of 448,560 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
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 ignoreElse strWhere = strWhere & ",'" & Me.Text30.Value & "'"End If If IsNull(Me.Text31) Then ' No criteria here - so ignoreElse strWhere = strWhere & ",'" & Me.Text31.Value & "'"End If If IsNull(Me.Text32) Then ' No criteria here - so ignoreElse strWhere = strWhere & ",'" & Me.Text32.Value & "'"End If If IsNull(Me.Text33) Then ' No criteria here - so ignore Else strWhere = strWhere & ",'" & Me.Text33.Value & "'" End IfIf IsNull(Me.Text34) Then ' No criteria here - so ignoreElse strWhere = strWhere & ",'" & Me.Text34.Value & "'"End If If IsNull(Me.Text35) Then ' No criteria here - so ignore Else strWhere = strWhere & ",'" & Me.Text35.Value & "'" End IfIf IsNull(Me.Text36) Then ' No criteria here - so ignoreElse 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 IfDoCmd.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 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 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 thatit 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 IfIf Not IsNull(Me.Text31) Then strWHere = "ACLike '" & Me.Text31 & "*" & "' Or "End If 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 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.