Passing a Query the Condition and Value from a Form (1 Viewer)

[ZanyJanie]

I've got a form with a combo box that I'm using to pass criteria to a query. Everything works great if I want to find records for a fixed condtion (e.g. = <> > < etc.) My problem is that I need to be able to pass both the conditional expression and criterial value to my query. Sometimes I will want my query to find records that are equal to a value; but other times, I will want records that are not equal, or are greater, etc. I created a second combo box on my form with the conditional expressions; but I've been pulling my hair out trying to figure out how I can pass both the conditional expression and data value to my query. If there's a way to do this, please HELP ME before I go bald!

Thanks,
Jane

P.S. I'm still a newbie who knows just enough VBA code to get myself in trouble. If code is required, an example would be greatly apreciated.

 

[RpbertS]

okay you want to pass 2 values from 2 differnt combo boxes to the query, correct?

in the queries criteria field why not jsut put like:

[forms]![formname]![cboname] And [forms]![formname]![cboname2]

that way the value in combo box one can be used as a criteria and the conditional values in the combo box2 can be used as well..

lemme know if that just doesnt solve anything =)

 

[DALeffler]

You're not going to be able to pass a text string from a combobox on a form to a query and have the query treat the text as a criteria statement (unless you're programatically running SQL statements).

If you have a combobox with the character string "<" on your form and have in your query criteria field something like, [Forms]![MyForm]![CboBox]&"ABCDE", that criteria statement will filter out all records that don't match for the string "<ABCDE". The query will not parse out the "<" as a logical operator.

What I think you need to do is pass the selections from the comboboxes to a function and have the function do the comparison operations, setting the return value of the function to true or false depending on those comparison operations, something like this:

In your query:

Field: TorF:Func1([forms]![myform]![cmbo1],[forms]![myform]![cmbo2])
Criteria: True

Then for Func1:

Function Func1(cmbo1 As String, cmbo2 as String) as Boolean
Func1 = False
'if cmbo2 = "<" then set Func1 true for less than
If cmbo2 = "<" Then
If cmbo1 < "ABCDE" Then
Func1 = True
Goto Done
End If
End If
'if combo2 = ">" then set Func1 true for greater than
If cmbo2 = ">" Then
If cmbo1 > "ABCDE" Then
Func1 = True
Goto Done
End If
End If

Done:
End Function

Or something like that...

Hope that helps...

Doug.

 

[RpbertS]

doh..dougs right about passing the logical operators..wasnt thinking straight


thanks for the catch