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

Searching with multi-selects and textboxes.

P: 38
Hello Bytes,

I hope this post finds you well on this wonderful Friday!

I've been kicking this code around for over a week now and cannot seem to find the correct syntax to handle all of the possible selections of my search form.

Would someone please help me correct my IF statements in this code used for searching.

Here's how it works.

I have a form with four or five unbound text boxes AND two multi-select list-boxes for criteria used in searching data.
What I cannot grasp is what my code needs to look like in order to handle the possible selections.

Every time I think I have it, I'll run another search with more or less criteria and it breaks.
Meaning, I'll add one thing in one textbox, and make two selections in the first list-box and search, it works.
Then I'll add one item in the second list-box and it breaks.
Or I'll add two items from the second list box and remove one from the first and it breaks.

I'm struggling with my 'If count > then mycriteria = and
If argcount > # then mycriteria =

I cannot work the logic out and it's killing me. Oh and I've learned that those parenthesis are crucial in getting back the right data.

Search Button Code
Expand|Select|Wrap|Line Numbers
  1.  
  2.   Public Sub Command4_ClickO
  3. 'On Error GoTo Err_View3lick
  4. Dim mysql As String, mycriteria As String, myrecordsource As String
  5. Dim argcount As Integer
  6. Dim count As Integer
  7. Dim i As Integer
  8. Dim x As Integer
  9.  
  10.  
  11.  
  12. mysql ="select * from [queryreport] where"
  13. addtowher [Find1], ''[ONS ID]", mycriteria, argcount
  14. addtowher [Find2], "[NOMENCLATURE]", mycriteria, argcount
  15. addtowher [Find3], "[LIN]", mycriteria, argcount
  16. addtowher [Find4], "[UICNEEDUNITNAME]", mycriteria, argcount
  17.  
  18.  
  19. 'first multi-select
  20. count = 0
  21. i = 0
  22. While i < Find8.ListCount
  23. If Find8.Selected(i) Then
  24. count =count + 1
  25. If count = 1 Then
  26. If argcount > 0 Then
  27. mycriteria = mycriteria & " AND"
  28. End If
  29. mycriteria = mycriteria & " ("
  30. Else
  31. mycriteria = mycriteria & " OR "
  32. End If
  33. mycriteria = mycriteria & "[G3 APPROVE] = """ & Find8.Column(O, i) & """"
  34. End If
  35. i =i + 1
  36. Wend
  37.  
  38. 'Second Multi-Select
  39. count =0
  40. x=O
  41. While x < Find9.ListCount
  42. If Find9.Selected(x) Then
  43. count = count + 1
  44. If count> 1 Then
  45. If argcount > 0 Then
  46. mycriteria = mycriteria & " OR "
  47. End If
  48. mycriteria = mycriteria & " ("
  49. Else
  50. mycriteria = mycriteria & " AND "
  51. End If
  52. mycriteria =mycriteria & "[STATUS] ='''''' & Find9.Column(O, x) & """"
  53. End If
  54. x=x+1
  55. Wend
  56.  
  57. If argcount = 1 Then
  58. mycriteria = mycriteria
  59. End If
  60.  
  61. 'if nothing specified return all
  62.  
  63. If mycriteria = "" Then
  64. mycriteria = "True"
  65. End If
  66.  
  67. myrecordsource = mysql & mycriteria
  68.  
  69. Me![subsearch].Form.RecordSource = myrecordsource
  70.  
  71. Exit_VIEW_Click:
  72. Exit Sub
  73.  
  74. Err VIEW Click:
  75. MsgBoxError$
  76. Resume Exit_VIEW_Click
  77.  
  78. End Sub
  79.  



Module Code

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub addtowher(fieldvalue As Variant, fieldname As String, mycriteria As String, argcount As Integer)
  3.  
  4. 'Create criteria for WHERE clause
  5. If fieldvalue <> "" Then
  6.  
  7. 'Add "and" if other criterion exist
  8. If argcount > 0 Then
  9. mycriteria =mycriteria & " And"
  10. End If
  11. mycriteria =(mycriteria & fieldname & " Like" & Chr(39) & fieldvalue & Chr(42) & Chr(39))
  12.  
  13. 'increase arg count
  14. argcount = argcount + 1
  15. End If  
  16.  

All the best Bytes and thank heavens it's Friday!

-Aaron
US ARMY
Dec 12 '08 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello, Aaron.

Well. Without getting deep into your code I could see that while you add left bracket to criteria string there is no code that add right bracket.
Did you try to debug your code?

Also, I recommend you to read the following article
Quotes (') and Double-Quotes (") - Where and When to use them.

Regards,
Fish.

P.S. The code doesn't look well structured - this results in duplicate similar code fragments and use of unnecessary variables.
Dec 13 '08 #2

P: 38
@FishVal
Thanks for the links, I'll spend some time with them on Monday, perhaps I'll be able to work it out.
I'm sure the code looks fragged to someone versed in vb, no doubt it's from my many attempts at trying to learn/make it work.

Kind Regards,
Aaron
Dec 13 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
Aaron,
I don't know if this is going to help or not, or whether the code works. I started this code when your post first appeared, then got distracted by clients, and did not finish my original thought until now. In the meantime you've changed your code. Hopefully, if nothing else, it may give you some ideas on how to approach your problem.

Expand|Select|Wrap|Line Numbers
  1. Public Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String) 
  2.  
  3. Dim ctrl as Control
  4.  
  5. 'The multiselect list box is trickier then textboxes because the user might have chosen  'more than one item, and you have to loop through the list box's items to see which ones 'have been selected:
  6.  
  7.  
  8.     '  Create criteria for WHERE clause. 
  9.     If Me.Ctrl = acTextbox and FieldValue <> "" Then 
  10.         '  Add "and" if textbox criterion exists. 
  11.             MyCriteria = MyCriteria & " and " 
  12.  
  13.        '  Add "Or" if listbox criterion exists.
  14.     ElseIf Me.Ctrl = acListbox and FieldValue <> "" Then
  15.                '  Append criterion to existing criteria. 
  16.                MyCriteria = MyCriteria & "("
  17.                       For Each [FieldName] In Me!lstBox.ItemsSelected
  18.                                MyCriteria = MyCriteria & "[FieldName]=" & Chr(34) & Me!lstBox.ItemData(FieldValue) & Chr(34) & " Or "
  19.                       Next
  20.                MyCriteria = MyCriteria & ")"
  21.              End If 
  22.   End If
  23.  
  24. End Sub
Dec 13 '08 #4

P: 38
Thank you very much for the code snippet.
I'm going to try an integrate it with what I have above.

Very Respectfully,
Aaron
Dec 15 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.