Using a form to display results from an existing query, based on variables in the form

[clendee]

Can anyone give me instructions or an online tutorial or whatever that
can show me how to display the results of a query in a from using variables that
I set…

For example, I’d like to have FromDate, ToDate and ClientName (variables) in the form
and the results from an already generated query be displayed in a table subform.

I guess I’d like to query the query using the above variables.

Many thanks,

Edit: Added 2/4/18 16:30 by clendee

This is the code that I am using in the SQL command:

SELECT
*
FROM
“qry1_JobMaterials”

WHERE
((UPPER(“Name”) LIKE ‘%’ || UPPER (:Var_LN) || ‘%’ ) OR (:Var_LN IS NULL))
AND
(( “Job Date” > :Var_FromDate ) OR (:Var_FromDate IS NULL))
AND
(( “Job Date” < :Var_ToDate ) OR (:Var_ToDate IS NULL))

The “Name” and “Job Date” are aliases in the query that I am accessing.

[Ratslinger]

Hello,

What you want to accomplish can be done with a filter table. This is a simple one record table which holds the contents of the variables selected. The sub form is based upon the query which uses these values in the WHERE statement. The ‘Refresh’ button needs to be on the sub form and not on the main form.

For example and further information, see my answer in this post → display specific record in a form.

EDIT 2018-04-03:

Additional sample where filter is based upon From Date, Through Date and Customer Name.

Sample: CustomerDateFilter.odb

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[clendee]

Thank you for the tip about the button, I notice that I can get this to work when I use a table but when I try to access a Query (qry1_JobMaterials using the ‘SQL Command’ option) in the SubForm_Lookup as per the image. It doesnt seem to like tke query that I am trying to access. I added an edit to my original question.

[Ratslinger]

Your SQL is nothing more than adding yet more parameters to be involved as input. Totally unnecessary. Have you read through the provided answer & linked info? Looked at the sample? All is done through the list boxes you have on ‘MainForm’. The selected items are stored in the filter table fields. You set a query based upon the values: Where UPPER(“Name”) LIKE ‘%’ || UPPER (Filter_table_value) || ‘%’.

[clendee]

Yes, I did read through the answer. I have just found the shipping invoice sample today, which is closer to what I am after (instead of the Bike colour sample). I’ll see what I can do.

[Ratslinger]

@clendee The samples are really secondary. They both operate the same but just display different information. The concept is what is vital. That is well explained in the first link of the linked to answer - Filter/Search with Forms (leveraging SubForms). If you can grasp that concept you can present filtering in many different ways. I have MANY variations of this as samples posted in this forum. Have attached another sample in my answer. All samples are same concept.

[clendee]

Thanks for taking the time to make sure I understand and for adding a second sample based on my request. I will re-read the inital link.