Microsoft Access Form Query

0
votes

I have created a form which allows users to search for data within the database via a form.

The database is restricted and the users are only able to navigate via buttons and forms.

At present, I have developed a form and a query which takes the criteria from a field within the form and then runs the query.

This is the code I use within the query to take the data from the form field:

[Forms]![Query].[refCriteria]

This code is featured under the "Criteria" section of the query design under the Ref field.

However, the query won't work when there is more than one criteria. I have tried entering the following code under the CCG field:

[Forms]![Query].[ccgCriteria]

I notice that when there are two or more instances of the above code within the same query, fails to produce any results.

Further to this, it prevents my users from querying multiple criteria which is certainly a feature that I require.

Is there a way to have any or all of the criteria potentially run via the same query? The only way I can think of getting this to work would be to create numerous queries, all of which would accept a different search criteria, but this is not a practical method.

1
ms-access

1 Answers

0
votes

Query will produce results if you enter both search criteria and you have rows with both fields equal search criteria. If you need to be able to search by just one criteria, leaving some of other search fields empty, use for each column criteria like this:

WHERE
[ref]=[Forms]![Query].[refCriteria] or Nz([Forms]![Query].[refCriteria],"")="" AND
[cgc]=[Forms]![Query].[ccgCriteria] or Nz([Forms]![Query].[ccgCriteria],"")=""

I'd recommend to type the criteria in SQL mode, in graphic mode Access will produce constructions much harder to understand, especially if you have more than 2 search fields.