How To Specify A Variable In A Query?

[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!

 

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

 

[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?