Solved

Create dynamic query form?

Posted on 2014-12-03
5
306 Views
Last Modified: 2014-12-04
Is there a way in Access to create a dynamic query form something like the attachment I've embedded?

Form example
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 167 total points
ID: 40479711
Certainly possible, but why do you want to do it? I ask because if you have an appropriate BOUND form you could use 'Filter by form' more quickly.

There are other threads and articles on EE that describe how to build dynamic criteria forms
e.g. Using a Criteria Form to Filter Records
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 167 total points
ID: 40479731
It's certainly possible.
Of course, the very best example of how to do it is Access's own Query Editor.
It's a superlative piece of UI.
What operational need would require going to the immense effort of recreating it?
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 166 total points
ID: 40480586
This question comes up quite a lot.

While it sounds like a great idea, ...after deeper consideration you will quickly realize that it will be more complicated than you expected.
Equals
Not Equals
Contains/ (Like)
Does not contain
Begins with
Ends with
Text searches
Numeric searches
Multi table Join queries
Aggregation
...etc

The Idea here is to create a "Natural Language" query builder for your users, ...correct?
The "simpler" you make it for your users, ...the more difficult this becomes for you to design.
Many "Natural Language" query builders have been created in the past, ...but none really ever caught on in the mainstream.
See here (http://en.wikipedia.org/wiki/Natural_language_user_interface)
...Specifically under the section called "Challenges".

You can certainly create something very "simple", ...but users will always want/need more.

These types of systems are typically based on "Filtering", ...not really based on building a complete query.
See Here (Parts 4 5 and 6)
http://www.fontstuff.com/access/acctut17.htm

In the end, you will realize that it is far easier to just educate your users on how to use the query builder.
Either that or create something "simple", ...then for any users who need more, ...you can teach them the query builder. (Where almost anything is possible)
;-)

Ultimately, ...for me at least, Learning SQL might be the solution...
SQL is more or less universal, ..so at the higher end, ...some things are just not possible in the Access QBE (Query By Example) grid, ...and can only be done with SQL.
;-)

JeffCoachman
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40480600
Not sure exactly what you need, but my Fancy Filters sample database might be helpful; it allows users to select values of various data types from combo boxes, and creates a filtered query, displaying the results in a subform.  Here is the link for downloading it:

http://www.helenfeddema.com/Files/accarch129.zip

and here is a screen shot of the form:
Fancy Filters form
0
 

Author Closing Comment

by:SteveL13
ID: 40480752
Thanks for the advise.  I'm going to use the built in Query Designer.
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question