Ask Your Question

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

asked 2018-04-02 15:47:28 +0100

clendee gravatar image

updated 2018-04-02 17:31:31 +0100

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,

image description

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-04-02 16:41:29 +0100

Ratslinger gravatar image

updated 2018-04-03 15:53:17 +0100


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.

edit flag offensive delete link more


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.

clendee gravatar imageclendee ( 2018-04-02 17:38:13 +0100 )edit

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) || '%'.

Ratslinger gravatar imageRatslinger ( 2018-04-02 18:11:35 +0100 )edit

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.

clendee gravatar imageclendee ( 2018-04-03 08:54:36 +0100 )edit

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

Ratslinger gravatar imageRatslinger ( 2018-04-03 15:49:38 +0100 )edit

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.

clendee gravatar imageclendee ( 2018-04-03 18:48:25 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-04-02 15:47:28 +0100

Seen: 447 times

Last updated: Apr 03 '18