Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Nz in Query (Access 97)

    i am having a problem that i have found help on, but the help does not seem to be working how it is supposed to... here goes

    in a table, i have a field for a date in which all are not filled in. i am trying to base a query on this to search for any given date (among other things).

    anyway, my criteria for the date field is this:
    Like Nz([Forms]![frmSearch]![txtDate],"0"])
    (txtDate.value is currently "*")

    the help that i found on this forum and in the access help file say this is how the criteria should be entered so as to not just return values which are not Null.. however, this syntax is not working. there are 45 records, but it is only finding the one with a date (not any without a date)

    i have tried many different options instead of "0" but none seem to work.

    Please Help!

    thanks in advance

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nz in Query (Access 97)

    AccessMan,

    If I understand what you want to do, the criteria should be :

    [forms]![form1]![text0] Or Is Null
    Francois

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nz in Query (Access 97)

    the only problem there is that if i enter a date in which i want to narrow my search criteria, like 4/30/82, it will find all that match that AND the ones without a date entered. what i need is that if there is a date entered, select only that, but if a no date is entered ( i will have it enter "*") then it should find all entries with a date and all entries without a date, thus the need for the Nz function

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nz in Query (Access 97)

    The * is a wildcard that, so far I know, only can be used in strings.
    I tested the following:
    Modify the field txtDate to Expr1: CStr(nz([txtDate];""))
    If you prefer you can add this expression in a new column in your query.
    In the criteria
    Like CStr(nz([forms]![form1]![text0])) & "*"

    Hope this help
    Francois

Posting Permissions

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