Re: Multiple search Criteria

Well, you can't do it that way. IF the field you are searching against always has a value (no nulls allowed) and the field is a text field then you can use criteria like the following.

LIKE NZ([Forms]![frmRunSites]![FindId],"*")

Alternative is to use
= [Forms]![frmRunSites]![FindId] or [Forms]![frmRunSites]![FindId] is Null

The problem with the alternative above is that if you use the construct many times the query will quickly grow too complex and will not run.

If the field is a date value and always has a value (no nulls) and you know the maximum range of the dates you can use something like the following.

Between NZ([Forms]![frmRunSites]![SomeDate],#1800-01-01#) and NZ([Forms]![frmRunSites]![SomeDate],#3000-01-01#)

The best way to handle this type of situation is to use VBA to build the criteria. Allen Browne has an example on his site - unfortunately I can't find the URL right now.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Gary S wrote:
If(IsNull([Forms]![frmRunSites]![FindId])=True,"like '*' or is null",[Forms]![frmRunSites]![FindId])

I am using this in the selection criteria of the query.
.

Relevant Pages

  • Re: Finding records by free text search
    ... Since you are searching for the string ... I have a query containing three fields: ... I have a form called frmSearch which contains the same three fields plus a ... The 'and' query criteria are ...
    (microsoft.public.access.queries)
  • RE: Null or No Null values in a parameter query
    ... IIf([Press ENTER for nulls, 'N" not nulls] Is Null,1,0) test if ... All criteria on the same line must be met -- they are AND'd together. ... I can see that a field called 1 was added to my query with a ... "KARL DEWEY" wrote: ...
    (microsoft.public.access.gettingstarted)
  • query criteria
    ... I'm using a query based on an imported table which uses two fields for date. ... I do not control and cannot change the data format of the table before it ... trouble when searching between years. ... I realize it is because the criteria is based on two different fields. ...
    (microsoft.public.access.queries)
  • Re: Create Form Run Query Display in Form
    ... query based on that form with a command button. ... The command button is linked to open the second form. ... searching, but I could not type in anything to search for. ... > on the Criteria line for each field that you want to search. ...
    (microsoft.public.access.forms)
  • Loop problem when time period loops from 1 to end of period again
    ... I have a dataset which I am searching by two fields, ... My goal is to write a query which will ... this query is generated in VBA given criteria from ... VBA, ...
    (microsoft.public.access.queries)