AND/OR statements (97)

Topic

New Reply

Hello, i received this code and it is suppossed to be used with an option button that when sleceted will take all the selected values from a list box and put them together, which will allow me to do queries bases on the AND. Will it work? alos, can i just change the word “AND” at the end to “OR” in order to incorporate a option button that when selected will put all selected values together as OR statements and allow me to query???? Thank you.

Reply | Quote

Replies

What code? Did you forget to include or attach the code?

Reply | Quote

forgetting the code might be a problem:

This simple code will pull the selected values from a list box, separate them by ” AND “, and append them to a string variable.

Dim x As Integer
Dim i As Integer
Dim strCriteria As String

For x = 0 To List1.ListCount – 1
If List1.Selected(x) Then
i = i + 1
strCriteria = strCriteria & x
If i < List1.SelCount Then
strCriteria = strCriteria & " AND "
End If
End If
Next x

Reply | Quote

what i actually need though is a AND/OR over an entire form. I have a form with 6 or so list boxes. i want to select 1 piece of info from each list box and then run my query based on my selections. I have that part programmed. i would like to add 2 check boxes to eah list box. 1 for AND and 1 for OR statements. I would the user to be able to select which list box values must be (AND) contained in the query and which ones are may be conatined (OR). It should let the user have full control on which dcriteria ia mandatory and which is optional. Make sense? this code does it within te listbox correct? i need one to set AND/OR properties for comparable listboxes against eachother not within 1 listbox. hope i didnt confuse you.

Reply | Quote

Use radio (option) buttons instead of checkboxes to select the operator.

Then just make your operators variables set by the radio button value.

LstBox1Op = iif(optBox1,”AND”,”OR”)
LstBox2Op = iif(optBox2,”AND”,”OR”), etc.

Then build your criteria string.

I’d create a function to that takes as arguments the listbox and the bitwise operator (and or).

strCriteria = MakeCriteria(lstBox1,LstBox1Op)
strCriteria = strCriteria & MakeCriteria(lstBox2,LstBox2Op)
etc. calling the function once for each list box.

Reply | Quote

Thanks Kevin,

I am not to familiar with VB and i really need this code. Do you think you could give me some more details on what EXACTLY to do. for example where does this code get entered within each option button? If it is the code per each option button if you could write out the full sample code then i can just paste and fix it for the other buttons. my problem is i can’t really tell what you are doing so i am completly lost. Thank you very much, this would solve a huge problem for us.

Ed

Reply | Quote

You don’t put it behind the radio buttons at all. All they are there for is to capture a flag that says “use an AND” or “use an OR” to join this condition to the others. In a single routine that actually runs the query, you would have code that reads the individual option groups and uses their values to build a critieria string. However, if this is a saved query, you have a somewhat different problem because you’ll have to find a way to pass the parameters to it.

Is this a saved query or are you creating it in code? If you’re creating it in code, what are you doing with it?

Reply | Quote

The query was made the normal way, i did not use code to create it. It is a basic query all i entered was teh field, table, show(check/uncheck), and the criteria and then repeated for each field. I think that is what you are looking for.

So i just add the radio buttons assign them names and do nothing woth them but place a caption for each that says “AND” and 1 that says “OR”. Alll the coding would be in my command button that executes the query. When entering the code i assume it would be:

Private Sub Command28_Click()

LstBox1Op = iif(optBox1,”AND”,”OR”) ‘ where LstBox1Op is the AND option button
LstBox2Op = iif(optBox2,”AND”,”OR”) ‘where LstBox2Op is also the AND option for the 2nd listbox

So I take it my OR codes that will be placed in my command button would be

LstBox3Op = iif(optBox1,”OR”, “AND”) ‘ where LstBox1Op is the 1st OR option button
LstBox4Op = iif(optBox2,”OR”,”AND”) ‘where LstBox2Op is the 2nd OR option for the 2nd listbox
??????

what the heck is this part doing:

strCriteria = MakeCriteria(lstBox1,LstBox1Op)
strCriteria = strCriteria & MakeCriteria(lstBox2,LstBox2Op)

My guess is it assigns the values in the 1st list box based on whther or not the option button is selected, to a string variable and then adds that to the selected options for the 2nd list box. Thus making a long string to perform the query. If there were more than 2 listboxes the next lines would read

strCriteria = MakeCriteria(lstBox1,LstBox1Op)
strCriteria = strCriteria & MakeCriteria(lstBox2,LstBox2Op)
strCriteria = strCriteria & strCriteria & (lstBox3,LstBox3op)
strCriteria = strCriteria & strCriteria & strCriteria & (lstBox4,LstBox4op)

I look at this and it makes no programming sense to me, i guess because i am not a good programmer. Am i on the right track at least?? So ill put this all into by command button on the event click and this will allow me to select multiple choices from a list box, decide which listboxes must(AND) / may(OR) be included in the query. Others have mentioned a foreign term to be called OLE that i will need, is this it? I hope so becuase i am lost enough as is. Thank you very much, happy new year!!!

Reply | Quote

I wasn’t asking how you created the query, I was asking whether it was a saved query (one you can see in the database window in Access) or if you were generating the SQL for the query in code and then running it.

MakeCriteria doesn’t do anything unless you’ve created the function as Kevin suggested. I believe his suggestion was intended to give you a function that would, when called, create that piece of the criteria string but would hide the ugly details. So instead of putting all that detail behind your command button, you would put code there tha calls MakeCriteria as many times as is required to generate the criteria string.

This line would call the function and pass it the listbox and the option button controls. The function would take those two controls and create and appropriate expression based on the value in lstBox1 and tack either an and or or onto the string based on the value in LstBox1Op.

strCriteria = MakeCriteria(lstBox1,LstBox1Op)

When you needed the second piece of the expression, you would call the MakeCriteria function again, passing it a different listbox and option button, and it would obligingly return a string based on those two controls. The concatenation simply means take whatever is already in strCriteria and concatenate the result I get from this function onto it.

strCriteria = strCriteria & MakeCriteria(lstBox2,LstBox2Op)

You would call it the same way each time, and you could even call it that way the first time, since strCriteria will be an empty string at that point. So for the 3rd listbox, your expression would be:

strCriteria = strCriteria & (lstBox3,LstBox3op)

Reply | Quote

OK charlotte,

So i think i have the part that checks each button and appropriate list box. Since each listbox has 2 possibel options (AND/OR) wouldn’t it look more like this:

strCriteria = strCriteria& MakeCriteria(lstBox1,LstBox1Op)
strCriteria = strCriteria & MakeCriteria(lstBox1,LstBox2Op)

OR am i doing something that says
IF lstbox1op = 1 then strCriteria = strCriteria& MakeCriteria(lstBox1,LstBox1Op)
IF lstbox2op =1 then strCriteria = strCriteria& MakeCriteria(lstBox1,LstBox2Op)

Siince it is a saved query what is this whole pasing of the parameters issue. Doesnt sound too tempting for me. I guess the biggest problem is what that function would look like, i am assuming it is a function called MakeCriteria and tells access to look in that listbox for the values selected and decide whether or not it is an AND/OR field to be included in the query? If i’m right (slim chance) how would you write something like that, are their reserved words that do this for you? Thank you.

Reply | Quote

Are you saying that each list box has an option *group* ? In that case, it only has one possible option–either AND or OR–and you can determine the value by referring to the group, which is what I assumed LstBox1Op was. Keep in mind that MakeCriteria will do whatever you need it to do. If I were writing it, I might create something like this:

Public Function MakeCriteria(ByRef lst As ListBox) As String
 
   Dim strField as String
   Dim varValue As Variant
   Dim strDelimiter as String

   varValue = lst.Value
  
  'Create the field name part of the criteria
  'based on which listbox was passed
  'Note:  this is where you could also set a delimiter
  '        these are just examples 
   Select Case lst.Name
        Case "lstBox1"
            strField = "[Field1]"
            strDelimiter = Chr(39)
        Case "lstBox2"
            strField = "[Field2]"
            strDelimiter = "#"
      ......
   End Select
 
   'now piece them together to get the string 
   MakeCriteria = strField & "=" & strDelimiter & varValue _
                            & strDelimiter 

End Function 

Then in the calling routine, I would do something like this:

strCriteria = strCriteria & Choose(Me!LstBox1Op.Value, ” AND “, ” OR “) & MakeCriteria(Me!lstBox1)

So If the field for lstBox1 was called Field1, and it was a text field, and you had clicked on the “Widget” item in lstBox1, MakeCriteria would return a string like this:

"[Field1]='Widget'"

If you had selected the AND option in your option group, the strCriteria expression would result in this:

" AND [Field1]='Widget'"

Then the next time you called MakeCriteria, you would be concatenating a similar expression onto the existing strCriteria.

I’m not clear on what you want to do with the AND/OR, so I just guessed that it was intended to be used between expression and indicates that the matching listbox value was supposed to be joined to the criteria with an AND or OR. I also had to guess at how your routine might know which field it was supposed to be using for the listbox. I don’t recall you including that information or anything about the datatypes involved. It it were me, I’d either include a zero-width column with the field name in it and another with a datatype indicator, although you could also use the listbox’s tag property to hold one or the other as well.

As to the parameters issue, how were you planning to use the criteria string once you created it? That has never been clear to me.

Reply | Quote