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.
Erika AndersonAccounting SystemsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
I think you want this.  It basically says if Store is NULL or Blank, then set this column to 0, otherwise use EndingBalance.


Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!!
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.