• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 34
  • Last Modified:

Handling Null Results in Ms Access Query, Nz not working

There are a lot of posts about this out there - I have a query that returns store numbers if they meet a date criteria. Example: Store 1234 closed 1/1/2018 - the data criteria is based off a form that a user enters fiscal periods. If the user enters a date range or quarter 1 through quarter 2 for 2018, Store 1234 would be returned. The store field is Store.

My issue is where there are no stores that closed in the date range. The query for that store list is blank, and so the associated query that is tied to general ledger balances for closed stores is blank. The General Ledger field is EndingBalance. So, the other queries that subtract those close stores amounts are not computing. They end up blank on the end result - the report. I need Ending Balance to be 0.00 if there are no stores.

I tried Nz([Store],[EndingBalance]=0.00) (with and without quotations on 0.00). I also tried just Nz([Store]). I tried Iff([EndingBalance]<>"",0.00,[EndingBalance]) but I don't think Access likes searching for Nulls with <> usually.
0
Erika Anderson
Asked:
Erika Anderson
  • 2
1 Solution
 
Bill PrewCommented:
I think you want this.  It basically says if Store is NULL or Blank, then set this column to 0, otherwise use EndingBalance.

Iff(Nz([Store])='',0.00,[EndingBalance])

Open in new window


»bp
0
 
Erika AndersonAccounting SystemsAuthor Commented:
Hey Bill....Nz worked once I added another table to somehow have at least a blank result in the Ending Balance and Store field (added a status column and right joined). Thank you!!
0
 
Erika AndersonAccounting SystemsAuthor Commented:
The moral of the story is Null doesn't mean nothing returned that is an applicable result. Null means at least a result with a blank field.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now