Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-18-2019, 02:09 AM   #1
BrageM
Newly Registered User
 
Join Date: Jun 2019
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
BrageM is on a distinguished road
Question Structure of query for multifield search form

I am pretty new to access, and I have no experience with VBA, I have only worked with the design view and to a lesser extent SQL. I am working on a project where I am making a search form for retrieval of data, which runs a query.
I am making a multifield search form in which there are a large number of unbound text boxes, two for each field. The purpose of the search form is to type parameters (min and max values) into some of these text boxes and retrieve the results that matches the search criteria.
I want the textboxes to return all results when left empty, and I want to be able to type in criteria in any pair of textboxes in any combination. I have found a way to make this work when there are only a few different fields, but with a large number of textboxes, this quickly becomes impossible due to the many combinations required. The SQL I have used looks something like this:
SELECT [Table 1].[Value 1], [Table 1].[Value 2]
FROM [Table 1]
WHERE ((([Table 1].[Value 1]) Between [Forms]![SearchForm]![Value 1 Min] And [Forms]![SearchForm]![Value 1 Max]) AND (([Table 1].[Value 2]) Between [Forms]![SearchForm]![Value 2 Min] And [Forms]![SearchForm]![Value 2 Max]))
OR ((([Table 1].[Value 1]) Between [Forms]![SearchForm]![Value 1 Min] And [Forms]![SearchForm]![Value 1 Max]) AND (([Forms]![SearchForm]![Value 2 Min]) Is Null))
OR ((([Table 1].[Value 2]) Between [Forms]![SearchForm]![Value 2 Min] And [Forms]![SearchForm]![Value 2 Max]) AND (([Forms]![SearchForm]![Value 1 Min]) Is Null))
OR ((([Forms]![SearchForm]![Value 1 Min]) Is Null) AND (([Forms]![SearchForm]![Value 2 Min]) Is Null))
What I am wondering is this: Can I rewrite this so that I donít have to account for every single combination with a line of code? Alternatively, is there a better way to go about this using some of Accessís other features?
Thanks!

BrageM is offline   Reply With Quote
Old 06-18-2019, 03:11 AM   #2
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,959
Thanks: 0
Thanked 463 Times in 459 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Structure of query for multifield search form

Instead of dynamic parameterized query, explore VBA approach. Review http://allenbrowne.com/ser-62.html
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 06-18-2019, 10:21 AM   #3
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,283
Thanks: 10
Thanked 2,251 Times in 2,203 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Structure of query for multifield search form

How's the returned data to be used? What's the purpose?

I would make a report based on Table1, use VBA to build a filter string based on the inputs and then use that filter string in a DoCmd.OpenReport that opens your report.

plog is offline   Reply With Quote
Old 06-18-2019, 11:34 PM   #4
BrageM
Newly Registered User
 
Join Date: Jun 2019
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
BrageM is on a distinguished road
Re: Structure of query for multifield search form

The purpose of the search is to show relevant data from a ground sample test.
So, if there are hundreds of tests taken in several different areas, you can search for certain concentrations of minerals/chemicals/molecules etc. and receive only the results that match the criteria of the search.
BrageM is offline   Reply With Quote
Old 06-19-2019, 01:43 AM   #5
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,547
Thanks: 88
Thanked 1,473 Times in 1,390 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Structure of query for multifield search form

You data structure is wrong.

The values you are searching should all be in the same field of one table.

Galaxiom is offline   Reply With Quote
Reply

Tags
form , multi field , query , search form

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multitable Multifield query bbulla Queries 3 01-20-2012 05:51 AM
Importing into a multifield iwantatransam Tables 3 04-27-2011 12:03 PM
Multifield Search lees02ws6 Forms 1 11-12-2009 05:12 PM
multifield keyword search georgvh Queries 5 09-07-2006 01:51 AM
Multifield Lookup Oldgye Forms 2 03-16-2003 01:14 PM




All times are GMT -8. The time now is 03:45 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World