Results 1 to 5 of 5
  1. #1
    Dave_D's Avatar
    Dave_D is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    67

    Forcing a zero where value is null

    My query is:


    SELECT Query1.CountofAcctno as expr1
    FROM Query1
    Where (((Query1.[Audit Finding]) Is Null));

    I'm trying to get expr1 to be 0 versus null as its result. I've tried adding "UNION Select 0" after expr1 but get "Query input must contain at least one table or query." Tried UNION ALL as well; same result.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Try using the NZ function, i.e.
    Code:
    SELECT NZ(Query1.CountofAcctno,0) as expr1
    FROM Query1
    Where (((Query1.[Audit Finding]) Is Null));
    Note that the NZ function returns a Text (String) result. If you want it to be numeric, just add zero to it, i.e.
    Code:
    SELECT NZ(Query1.CountofAcctno,0)+0 as expr1
    FROM Query1
    Where (((Query1.[Audit Finding]) Is Null));
    Here are some details on that NZ function: https://www.techonthenet.com/access/...dvanced/nz.php

  3. #3
    Dave_D's Avatar
    Dave_D is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    67
    I tried it both ways (with and w/o +0) as you suggested and both times my results were the same, NULL. Did not get 0!

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Note that the NZ function returns a Text (String) result
    If I'm not mistaken,
    Nz returns the variant if the variant is not null.
    Nz returns the value_if_null if the variant is null.
    So if the variant is not null and is text, it returns text data type. If it's numeric, it returns a number, and so on.
    This NZ(Query1.CountofAcctno,0) will return the number zero if CountofAcctno is Null.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Is your query returning any records at all?
    The NZ function converts a Null to some other value for any record returned. But if your query is not returning any records, there is nothing to convert.

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

Similar Threads

  1. Replies: 4
    Last Post: 05-10-2013, 01:22 PM
  2. forcing the user to choose a value
    By fabiobarreto10 in forum Forms
    Replies: 16
    Last Post: 04-15-2012, 09:52 PM
  3. Forcing users to use DB via system.mdw
    By simon955 in forum Programming
    Replies: 1
    Last Post: 01-30-2012, 02:15 PM
  4. Forcing a Report to Expand Horizontally
    By Rawb in forum Reports
    Replies: 3
    Last Post: 06-23-2010, 08:36 AM
  5. Forcing text to upper case
    By barrettp in forum Forms
    Replies: 10
    Last Post: 06-01-2010, 09:44 AM

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