Variables in query statements..

[Mitch_____W]

Is it possible to use variables (declared and defined with code) within query fields and/or query criteria?

I have tried to do this and haven't had any luck without utilizing an unbound textbox to assign the value of the variable and then refer to the textbox in the query. Seems like a waste to go about it like this since I don't need the variable value to be displayed ever, hence the textbox.visible property I have to set to False.

Hope this makes some sense to someone....

Mitch

 

[RichMorrison]

Yes and no.

You can't use variables but you can use functions that return the value of a variable in criteria of queries.

If you have a book on Acc97 or Acc2K, look in the index for "property procedures".

RichM

 

[UniqueTII]

That sounds a lot like something I had to do. Can you give an example of where you'd need a variable and what you'd use to calculate it?

 

[Mitch_____W]

Lets say, for instance, you have a form with five unbound textboxes. then you have a query which has six fields (five of whose criteria is each of the unbound textboxes values respectively). Now lets say the user only puts values in two of the unbound textboxes and runs the query from the form control. First of all you only want the fields displayed to the user which values where entered as well as the field which has no criteria, and second you need to get around the query returning no records due to the other three criteria being recognized as being nulls and since the criterias are "AND" not "OR". You want the user to be able to query in any way they wish from the 5 criteria textboxes on the form. The only way I have been able to do this so far is through A LOT of different queries and a LOT of code... Seems like a I should be able to do this with one query if I can use variables from 'select cases in code' for the feild criterias...

Hope you follow me on this one...

There have been similar situations but more complex that I could use this "query based on variables" scenario.

Thanks in advance!!!

Mitch

[This message has been edited by Mitch_____W (edited 05-03-2002).]

 

[RichMorrison]

Mitch_W wrote:
<<
There have been similar situations but more complex that I could use this "query based on variables" scenario.
>>
Sounds like a different question. Using variables in query criteria uses the method described previously.
1) make a module
2) Dim a Private variable
3) make 2 Functions, one to set a value in the private variable and one to get the value
4) use the set function from code in a form, get the value from user input or a table
5) user the get function in the Criteria box

For "optional" criteria; you can make that work if the varaibles are Strings.
1) use "Like GetValue()" in criteria
2) if no value supplied by user, set variable to "*". that gets anything.

RichM

 

[Mitch_____W]

Ohhhhhhh!!!!

Thanks Rich!!!

It has just clicked in my head!!!

query criteria.... problem resolved....

However, I still am unaware of how to set up the query to only SHOW the feilds which are relevant to what the user requests from the form....

For instance:

Query has 3 fields:

[Name]
[Data1]
[Data2]

Form has two combo boxes and command button:

[Data1]
[Data2]
cmdRunQuery (OnClick event)

User selects value from combobox [Data2] and clicks command button...

At this point the query would show all 3 fields... I want the query to only show the [Name] and [Data2] since the user only selected a value from [Data2] combobox.

I hope I am describing this understandably....

Mitch

 

[RichMorrison]

you wrote:
<<
At this point the query would show all 3 fields... I want the query to only show the [Name] and [Data2] since the user only selected a value from [Data2] combobox.

I hope I am describing this understandably....
>>
Yup that's understandable.
It's also different.
We have covered optional parameters.
You also want a variable number of selected fields based on what criteria the user did/did not supply.

My first idea is "don't do that"

Select the same fields all the time regardless of criteria. Is that a problem ?

But if you are determined then the simplest way is this:
1) make one select query with all fields and criteria
2) make secondary queries that select from the first query. Let each secondary query select the fields you want.
3) put code in your form that determines which secondary query to run base on which criteria the user supplied.

Sort of messy but can be done.

RichM