Results 1 to 3 of 3
  1. #1
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92

    How do I make expression field remain empty if expression equals zero?

    Hi everyone. I have created a query to use when generating a report. I have two fields in my query that I want to add together in an expression, so the sum will show up in one field of my report. The two fields are 2XTotalAmt and 3XTotalAmt. Both are formatted as currency on the table they exist on, and when I view my Query in datasheet view, the numbers in those fields show up in the currency format.

    In my query, I created an expression like so: 2X3X: Format(Nz([2XTotalAmt])+Nz([3XTotalAmt]), "Currency")



    This works -- however, if both 2XTotalAmt and 3XTotalAmt are Null, I want 2X3X to also be Null (store no value). Is there a way to do that? Right now it is storing $0.00, but I don't want that to show up on my report if the total is zero. If there is no way to make the field store no value, is it possible to have it not show anything in that field on the report?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps:
    2X3X: Iif(Nz([2XTotalAmt])+Nz([3XTotalAmt])=0,Null,Nz([2XTotalAmt])+Nz([3XTotalAmt]))
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    That did it! Thank you!!

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

Similar Threads

  1. Replies: 6
    Last Post: 06-30-2017, 10:46 AM
  2. Replies: 4
    Last Post: 05-03-2016, 01:51 PM
  3. Make Table Query fails with invalid expression
    By BBrayton in forum Queries
    Replies: 11
    Last Post: 01-06-2015, 06:10 PM
  4. Replies: 2
    Last Post: 11-20-2012, 03:21 AM
  5. Replies: 4
    Last Post: 10-26-2012, 12:49 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