Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2012

    Unhappy Unanswered: Query for null-value in numeric field


    One of the fields in my table is numeric and can have no value or a numeric value.
    I also have a form where users can enter the criterium OR can leave that criterium-field empty.

    In case they enter a numeric value, my query retrieves all the matching records. So far so good. (criterium =forms!critform!critfield)
    But when they leave that criterium-field empty, I get no records. BUT I WANT ALL THE RECORDS, those with a numeric value but also those without a value.

    With a text-field, I use the like "*" & forms!critform!critfield & "*" but this doesn't work with numeric fields.
    When I test my query with the criterium Is Null or >0 I get all the records. So I know this works too.
    But when I try the criterium Nz(forms!critform!critfield;Is Null or >0) I don't get any record. Changing it to Nz(forms!critform!critfield;"Is Null or >0") gives me an error.

    How can I get the Is Null or >0 as criterium if the criteriumfield is left empty?

    Thanks for helping!


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    can we see the query?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2012
    Here's a simplified database with some records I use to test (my other database I'm using contains confidential data)

    Some field-types are in Dutch (but I added some explanation in English...)
    Attached Files Attached Files

Posting Permissions

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