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!
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!