How To Specify A Variable In A Query? (1 Viewer)

[Heatshiver]

I have a query that has three fields: 1) Days 2) Bit Size 3) Feet. The Days field has a date range specified in the Criteria that comes from two unbound fields on a form. I have a second query based on the first: Two fields are Days with the Total as Min and Max. Three fields are Feet with the Total as Min, Mac, Avg. One field is Bit Size with the Criteria as a specified bit size (i.e. 6.125). These queries are used on a report, and all data is inputted from forms prior to the report.

The queries work as expected with a specified bit size. My problem is that while Bit Size is normally chosen from a list of sizes, a custom size can be inputted. I am unsure how I can specify the Criteria in Bit Size to be a custom size..? To compound things further, more than one custom size can be used.

A) I need a way to pick out the custom sizes from the first query and B) use them as the Criteria for the second query. If I use VBA, I assume I could complete B) by using a WHERE clause (if the custom size(s) has been defined)... Any help is much appreciated!

 

[Pat Hartman]

I'm not sure I understand your question. Are you asking how to run a report for a specific bit size?

If you are using a form to gather the criteria for running a report, you can use an unbound combo with its limit to list property set to no. This will allow you to type in a value that is not in the combo's RowSource.

A) I need a way to pick out the custom sizes from the first query and B) use them as the Criteria for the second query. If I use VBA, I assume I could complete B) by using a WHERE clause (if the custom size(s) has been defined)... Any help is much appreciated!

A) you don't pick from a query. You pick from a combo on a form. The combo has a RowSource and that will be a query that selects the items to display in the pick list.. B)The criteria for the query should come from a form.

When you use the OpenReport method, specify a WHERE argument that references the form field.

 

[Heatshiver]

Thanks for the reply Pat. I may have not explained what I was trying to achieve as well as I liked.

Moreorless, there is a listbox for the Bit Size field on a form. However, the user is not limited to the listbox as they can input their own Bit Size if it is not on the list. On the report, when the first query runs it'll show all the Bit Sizes for the date range supplied. Most of the Bit Sizes should correspond to the listbox, but there may be a custom size or two not from the list. I need a way to somehow pluck those out and use those in the Criteria of the second query. Essentially, I need the custom sizes somehow defined so that I can use them in Criteria of the Query (WHERE clause of the SQL statement, if necessary).

I have attached a small version of the DB with just a table and the queries to show what I am doing. It will ask for a start date (04/12/2013) and an end date (09/17/2013) for the first query. If you run that query, you will see there are 3 Bit Sizes over 4 dates. 2 of the Bit Sizes are from the list, while "3.24" is a custom size not on the list. I need to somehow get that custom size so it can be used as Criteria in the second query. The second query presently has the Criteria as "6.125", but this is known because of the predefined list. There will be multiple ("second") queries for each Bit Size.

If something can even be done before the query runs to specify the custom sizes (without needing additional input from the user), that would work for me as well.

 

[Pat Hartman]

You shouldn't need a separate query for each size. Size is a variable and should be entered when the query runs.

If you want one query to control the selection in another query, you can create a third query that joins the two.

Perhaps your example is too stripped down but I don't understand why you couldn't just use the date criteria in query2.

 

[Heatshiver]

Thanks for the reply.

Your definitely right about the multiple queries for the Bit Size. I just used a group and sort on the Bit Size to get them all listed as wanted.

There had been a problem with totals since it would use all and not for each Bit Size, but I just had to pass through the Bit Size to the 2nd query.

 

[gemma-the-husky]

two choices really

1. get the value form a control on a form
in the criteria row of the query, put forms!formname!controlname

2. get the value from a public variable. the thing about this is that you cannot use the variable directly - you have to "read it" with a function
in the criteria row of the query, put =somefunctionname()

 

[Heatshiver]

Gemma, very interesting. I can't use the first as they input the information on the form, but this query is only used on the report.

However, I am very intrigued by your second method. How would this work with more than one custom Bit Size?