Can you use a variable in a query??

[jlgreen]

Hi Everyone,
I have a subreport in my database that is based on a query. In order for the report to run properly it has to get a value from a text box on another form. I used [forms]![form_name]![textbox_name] in the criteria in the design view of the query in order to get the value. I have this in my query and it works perfectly however, I now want to work something new into my database. Because of this change the value the query needs to get could be on one of two separate forms. For example if the user chooses to view a quote by Company name the query needs to get the value off the frmCompany form so I'll have to use [forms]![frmCompany]![txtQuoteNo]. If the user wants to view the Quote by Last Name then the query will have to get the value off the frmLastName form so the criteria expression would now have to be [forms]![frmLastName]![txtQuoteNo]. Obviously I can't have both criteria expressions at once.

So here is what I am trying to do, I want to pass a variable to the query instead so I can assign the Quote Number to this variable and then have the query run using the variable instead of the reference to the text box.

Whew! I hope that makes sense to someone out there. I anyone has any advise or suggestions it would be greatly appreciated.

Janet

 

[tempclerk]

Actually, you could use both criteria at once, using IIf and the IsLoaded function that used to come with the Northwind sample database or something like that, but the variable way is probably better. I'm sure someone can help you with it.

 

[jgoodman00]

If you construct the query from code, you can pass in the variable with no trouble. However, as far as I am aware you cannot pass variables into queries (the ones which show in the queries tab).

For this reason you may have to execute this query from code. Obviously, this then presents another problem, in creating a suitable record-source. You could work around this by creating a temporary results table & then appending the successfully returned records (from a docmd.runsql command) to this table. You then set your subreport's record source to this table & hey presto... James Goodman
j.goodman00@btinternet.com

 

[Sawatzky]

Dear Janet,

Using a variable (parameter) in your query would cause access to popup an input-window and ask your user to input the param value.

What you can do is
1) either use the iif-syntax tempclerc suggested or
2) refer to a public function (that you have to create) in a modul that gives back the value you want.


HTH
regards Astrid

 

[BoxHead]

A few more options:

Since you're calling this query from two different forms, how about two queries? Copy the one you have, change the criteria and run it off the second form.

I think that the best solution would be to run the SQL from code and declare a variable.

On the QBE grid, right-click on the top (gray) portion of the window. Select "SQL View" and copy the text. Go to the design view of the first form, and paste the SQL as the source for your subform. Go to the second form, repeat the paste and then change the WHERE reference to point to the appropriate form.

If you have a switchboard form where the users are selcting "by company" or "by last name" you could hide it instead of closing it. Put a textbox on this form and set its visible property to false. On the update events of the other two forms' txtQuoteNum textboxes, run

    Form_frmSwitchBrd.Textbox1 = ThisForm.txtQuoteNum

Use the new textbox as the criteria for the query. If you have to make other changes to the DB and add other options for the criteria, you can feed them into this textbox.

HTH

John