Results 1 to 3 of 3
  1. #1
    Middlemarch is offline Competent Performer
    Windows 10 Access 2019
    Join Date
    Mar 2015

    Trouble with Null value in query

    SELECT  Nz([Mode]) AS Expr1
    FROM qryBB
    WHERE  ((Nz([Mode]))<>4;
    This works in Access but I need it to work without Nz()
    Have spent hours trying IsNull Is Not Null etc but getting nowhere.
    Can anyone help? Thanks.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Why do you need it to work without the nz function? If in a different environment, there are usually alternates

  3. #3
    Join Date
    Apr 2017
    Why Nz([Mode])?
    As you compare [Mode] not equaling with 4 or contrary, we can assume that in source table Mode has some numeric value or is Null. Nz() without optional ValueIfNull argument determined returns an empty string, i.e. "", when Mode is Null. So you'll check is "" <> 4 then!!! And you want for some records Mode to be returned as a string, and for other records as a number!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-07-2019, 10:46 AM
  2. trouble with query and null recordset
    By Mandamus in forum Access
    Replies: 9
    Last Post: 09-11-2017, 05:02 AM
  3. trouble with Null values and et al?
    By mattf in forum Access
    Replies: 9
    Last Post: 03-05-2014, 10:19 PM
  4. Replies: 17
    Last Post: 01-26-2014, 06:53 AM
  5. Replies: 1
    Last Post: 02-23-2012, 02:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums