By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Adding Null fields

AdminCyn
P: 4
I am trying to create a report to simulate an invoice. I have created a Select query to join all the tables/fields into one. That is working all good. I have set the Format to be 0;0;0;0 so that if it is a null field it prints a "0" instead. My issue is when there is a null value in a list of five number I want to total, I get back another null value; here is what my report looks like




How do I tell it to ignore the null value fields and add the ones with values?
Apr 20 '10 #1

✓ answered by ADezii

@AdminCyn
If any part of an Expression is NULL, than the entire Expression will evaluate to NULL. Put differently, NULLs will propagate through Expressions. You can use the Nz() Function to return a 0 should a Field be NULL, as in:
Nz(Field1) + Nz(Field2) + Nz(Field3) + Nz(Field4)...
That being said:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Nz(75) + Nz(NULL) + Nz(25) + Nz(NULL)
will return
Expand|Select|Wrap|Line Numbers
  1. 100

Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,597
@AdminCyn
If any part of an Expression is NULL, than the entire Expression will evaluate to NULL. Put differently, NULLs will propagate through Expressions. You can use the Nz() Function to return a 0 should a Field be NULL, as in:
Nz(Field1) + Nz(Field2) + Nz(Field3) + Nz(Field4)...
That being said:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Nz(75) + Nz(NULL) + Nz(25) + Nz(NULL)
will return
Expand|Select|Wrap|Line Numbers
  1. 100
Apr 20 '10 #2

AdminCyn
P: 4
Brilliant! Works Perfectly :O)
Apr 20 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.