Using a public variable as criteria in a query

[diversoln]

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 ?

 

[SteveA]

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

 

[diversoln]

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......

 

[diversoln]

Okay - I guess I need to goto the SQL view for the query instead of the design view.

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

 

[diversoln]

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).]

 

[diversoln]

Pat - thanks for setting me straight - I've got the query working great now & have learned a lot in the process.