Structure of query for multifield search form (1 Viewer)

BrageM

New member
Joined
Jun 18, 2019
Messages
2
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! :eek:
 

plog

Banishment Pending
Joined
May 11, 2011
Messages
9,573
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.
 

BrageM

New member
Joined
Jun 18, 2019
Messages
2
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.
 

Galaxiom

Super Moderator
Staff member
Joined
Jan 20, 2009
Messages
11,817
You data structure is wrong.

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

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom