• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 21
  • 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

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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