Erika Anderson
asked on
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,[Ending Balance]) but I don't think Access likes searching for Nulls with <> usually.
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]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER