Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246

    Unanswered: Null criteria replacement

    Hi,
    I have the following crtieria in a query where I want the criteria to search for anything if the form criteria is null and if not then only for that criteria.

    IIf(IsNull([Forms]![Policies_frm]![RiRefSearch]),Like "*",[Forms]![Policies_frm]![RiRefSearch])

    But it doesn't seem to be working, what am I doing wrong?

    Regards
    John

  2. #2
    Join Date
    Jul 2004
    Posts
    64
    Take a look at the Nz function and example. It should do what you need, although I haven't tested it yet.

  3. #3
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    NZ([Forms]![Policies_frm]![RiRefSearch],[TableName].[FieldName])

    FieldName = name of field you are applying the criteria to.
    TableName = name of table that FieldName is from.

    This will return all records where [TableName].[FieldName] is not Null.
    Using Like "*" as criteria only returns non-null records so I assume that is what you wanted.

    Hope this helps!
    Last edited by Cosmos75; 07-21-04 at 11:09.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  4. #4
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154

    Just in case...

    Just in case you do want to return ALL records if no criteria is entered then enter this in criteria row (query design view).

    [Forms]![Policies_frm]![RiRefSearch] OR [Forms]![Policies_frm]![RiRefSearch] Is Null

    In SQL view, the SQL statement should look something like this.

    SELECT Table1.Field1, Table1.Field2
    FROM Table1
    WHERE (((Table1.Field1)=[Forms]![Policies_frm]![RiRefSearch])) OR ((([Test]) Is Null));


    After you save and reopen the query, the query should have the correct SQL.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  5. #5
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Sticker if you like SQL check this link out
    http://support.microsoft.com/default...roduct=acc2002
    Ryan
    My Blog

  6. #6
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Thanks for the replies this seems to work but now I have a further problem.

    I've made a button the removes the filter but now if I use the combo's to filter the data, no data is returned, yet if I exit the form and then use the comb's with the same criteria, data is returned...........what gives??

    John

  7. #7
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    This is very strange I've found that the problem is the button that removes the filter.
    If I use the toolbar command to remove the filter/sort all is well, but using the buttton causes there to be no data returned and also causes a "The applyfilter action was canceled" if I use the search button.
    The code I'm using for the remove filter button is
    RunCommand acCmdRemoveFilterSort

    Am I missing something?

    Regards
    John

  8. #8
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Are you using a query to view data or to populate a form? If you are a populating a form via a QBF (query by form) using criteria entered in [RiRefSearch], why the need for a filter button?
    Last edited by Cosmos75; 08-05-04 at 10:07.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  9. #9
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi
    Yes I'm using a query as a filter to populate the form as determined by the user.
    Regards
    John

  10. #10
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Is this what you are trying to accomplish? Enter A, B, or C in the filter txtbox and click the button labeled "Filter" to requery the subform. Click "Remove", sets the filter to Null and requeries the subform.
    Attached Files Attached Files
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •