Results 1 to 4 of 4
  1. #1
    metsmaniac is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Posts
    10

    Subtracting field in one query from field in another query - access 2013

    I am trying to subtract a numeric field in one query from a numeric field in another query.

    The two queries are joined, with the first query being the primary. It contains more records than the 2nd query.

    If there is no corresponding record in the 2nd query, the result is blank even if the 1st query contains a number.

    For example, the 1st query has a record with a value of 100.
    There is no corresponding record in the 2nd query.
    The result is a blank cell rather than 100.



    How do I get the math to work?

    Thank you.

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You can use the NZ function to replace an unmatched value with a zero, so the math will work, i.e.

    Expr1: [Table1].[Field1] - NZ([Table2].[Field1],0)

    That tells Access if there is no matching field in table 2, use a zero (so you can do the math).

    See here for more on NZ: http://www.techonthenet.com/access/f...dvanced/nz.php

  3. #3
    metsmaniac is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Posts
    10
    You are brilliant, JoeM. Thank you very much - it worked.

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Glad to help!

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

Similar Threads

  1. Replies: 7
    Last Post: 02-17-2014, 10:40 PM
  2. Access 2013 query from mssql.
    By Rzadziu in forum Queries
    Replies: 2
    Last Post: 12-04-2013, 02:45 PM
  3. How to create an Iif field in Access 2013
    By buick1910 in forum Access
    Replies: 9
    Last Post: 10-27-2013, 05:59 PM
  4. SQL Query challenge in Microsoft Access 2013
    By Bingfoot in forum Queries
    Replies: 5
    Last Post: 10-07-2013, 07:58 AM
  5. Replies: 1
    Last Post: 05-09-2013, 07:54 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