Searching with multi-selects and textboxes.

[aaronkmar]

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

 
  Public Sub Command4_ClickO
'On Error GoTo Err_View3lick
Dim mysql As String, mycriteria As String, myrecordsource As String
Dim argcount As Integer
Dim count As Integer
Dim i As Integer
Dim x As Integer
 
 
 
mysql ="select * from [queryreport] where"
addtowher [Find1], ''[ONS ID]", mycriteria, argcount
addtowher [Find2], "[NOMENCLATURE]", mycriteria, argcount
addtowher [Find3], "[LIN]", mycriteria, argcount
addtowher [Find4], "[UICNEEDUNITNAME]", mycriteria, argcount
 
 
'first multi-select
count = 0
i = 0
While i < Find8.ListCount
If Find8.Selected(i) Then
count =count + 1
If count = 1 Then
If argcount > 0 Then
mycriteria = mycriteria & " AND"
End If
mycriteria = mycriteria & " ("
Else
mycriteria = mycriteria & " OR "
End If
mycriteria = mycriteria & "[G3 APPROVE] = """ & Find8.Column(O, i) & """"
End If
i =i + 1
Wend
 
'Second Multi-Select
count =0
x=O
While x < Find9.ListCount
If Find9.Selected(x) Then
count = count + 1
If count> 1 Then
If argcount > 0 Then
mycriteria = mycriteria & " OR "
End If
mycriteria = mycriteria & " ("
Else
mycriteria = mycriteria & " AND "
End If
mycriteria =mycriteria & "[STATUS] ='''''' & Find9.Column(O, x) & """"
End If
x=x+1
Wend
 
If argcount = 1 Then
mycriteria = mycriteria
End If
 
'if nothing specified return all
 
If mycriteria = "" Then
mycriteria = "True"
End If
 
myrecordsource = mysql & mycriteria
 
Me![subsearch].Form.RecordSource = myrecordsource
 
Exit_VIEW_Click:
Exit Sub
 
Err VIEW Click:
MsgBoxError$
Resume Exit_VIEW_Click
 
End Sub
 

Module Code

 
Public Sub addtowher(fieldvalue As Variant, fieldname As String, mycriteria As String, argcount As Integer)
 
'Create criteria for WHERE clause
If fieldvalue <> "" Then
 
'Add "and" if other criterion exist
If argcount > 0 Then
mycriteria =mycriteria & " And"
End If
mycriteria =(mycriteria & fieldname & " Like" & Chr(39) & fieldvalue & Chr(42) & Chr(39))
 
'increase arg count
argcount = argcount + 1
End If  
 

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

-Aaron
US ARMY

[FishVal]

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.

[aaronkmar]

@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

[puppydogbuddy]

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.

Public Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String) 
 
Dim ctrl as Control
 
'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:
 
 
    '  Create criteria for WHERE clause. 
    If Me.Ctrl = acTextbox and FieldValue <> "" Then 
        '  Add "and" if textbox criterion exists. 
            MyCriteria = MyCriteria & " and " 
 
       '  Add "Or" if listbox criterion exists.
    ElseIf Me.Ctrl = acListbox and FieldValue <> "" Then
               '  Append criterion to existing criteria. 
               MyCriteria = MyCriteria & "("
                      For Each [FieldName] In Me!lstBox.ItemsSelected
                               MyCriteria = MyCriteria & "[FieldName]=" & Chr(34) & Me!lstBox.ItemData(FieldValue) & Chr(34) & " Or "
                      Next
               MyCriteria = MyCriteria & ")"
             End If 
  End If
 
End Sub

[aaronkmar]

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

Very Respectfully,
Aaron