Can a public variable be used as the criteria in a query ?

What I'd like to do is have the user fill in a form to identify criteria for a select query. At times, the user may decide not to limit one or more of the fields at all.

For example, the user may want to limit the items displayed by using a defect code and the inspection cycle (both numeric data) as the criteria. However, he may then want to search for all defect types found at inspection cycle #1. Therefore, I'd want my criteria for defect code to be ">0"

When I use my public variable name in the criteria, the query seems to think I'm typing in text. It doesn't recognize that its a variable and gives me a data type mismatch error.

I've often used criteria like forms!myform!defectcode but, how could I set this up to accept numeric and text options ?

Any ideas ?

Hi,

Pat answered this question for you a couple of weeks ago. Please refer to the following link: http://www.access-programmers.co.uk/ubb/Forum3/HTML/002363.html

My apology for sounding repetitive. Maybe I need to reword my question. I was wondering if the public variable or function can be used as criteria right on the query form.


Also - if I do need to go the route of using the code Pat described, can this be done in an event procedure ? I'm not sure how to implement the SELECT statement in an event procedure.

Sorry if these questions seem basic. Being self taught I feel like I'm working at a variety of levels - good at somethings but a real beginner at others......

Okay - I guess I need to goto the SQL view for the query instead of the design view. http://www.access-programmers.co.uk/ubb/wink.gif

I work with this for a while... Thanks.

Once you create the public function in a standard module (It MUST be a PUBLIC function in a STANDARD MODULE or the query will not "see" the function because of the way objects/variables are scoped in Access), you can use it as I described. To use it in the QBE grid, put it on the criteria line under the field you want to compare to:

=YourFunc()

Now that I've been working with this a bit, I'm still having trouble with 2 issues. The first is getting the query to evaluate an expression such as ">0" as the criteria. If I simply type >0 in the grid, it works fine but if I try to set a variable to equal >0, it doesn't work. It says "the expression is typed incorrectly or is too complex to evaluate"

Secondly, even though I set up my functions as PUBLIC functions in a Module, I get an "undefined function" error when I run the query.

Any ideas ? Thanks.


[This message has been edited by diversoln (edited 11-01-2001).]

You CANNOT pass a variable with a value of ">0" and expect it to be evaluated properly. Access is treating the criteria as a text literal. So, it is evaluating the statement as if you had typed:

Where somefield = ">0"

Which is of course not what you wanted. If you want to make a query where you can substitute the ">" operator for the "=" operator, you need to build it in VBA. You need to build a query with code that looks something like:

Dim strSQL as String
Dim strRO as String

If condition1 Then
strRO = "="
Else
strRO = ">"
End If

strSQL = "Select * From YourTable Where SomeField "
StrSQL = StrSQL & strRO & " " & SomeOtherVariable & ";"

Then use the RunQuery or Execute method to run the query. Or, place it in the recordsource of a form or report.

Pat - thanks for setting me straight - I've got the query working great now & have learned a lot in the process. http://www.access-programmers.co.uk/ubb/smile.gif