Handling Null Values in MS Acess Query


I need some assistance with a MS Access query.

The database that I am currently working on was designed to track the Current Year budgeted and actual volumes for a particular market.  The financial year starts in April and, to date, I have entered stats for April - July thus leaving the fields for Aug - Mar blank (please refer to the bottom of tbl_Statistics).

In Query1, the aim of this query is to track and compare the budgeted volumes vs. the actual volumes and calculate some running totals for the CYTD and PYTD.

My challenge is, for the CYTD Actual BBLS, I prefer that the fields remain blank if there is no value in the CY Actual BBLS column; so it should look like this:

Month     CY Actual BBLS          CYTD Actual BBL
Apr          5,922.16                       5,922.16
May         5,363.49                      11,285.65
Jun          5,487.57                       16,773.22
Jul           5,976.27                        22,749.49

In other words:
if CY Actual BBLS is Null
CYTD Actual BBLS should be Null
else run a DSum formula

This does not seem to be working.

In "Copy of Query1", I tried running a simple IIF formula that instructs the query to return 0 if the field is null and 1 if the field is not null.  The formula returned all 1's.

Can someone please assist with this?

I bet ya that the solution is quite easy but I can't figure out what I am doing incorrectly.


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.

test: IIf(IsNull([Statistic_ActualVol]),0,1)
There is no field with name CY Actual BBLS in your sample, but there is Statistic_ActualVol
omgangIT ManagerCommented:
Change the expression for CYTD Actual BBLS to

CYTD Actual BBLS: IIf(IsNull([CY Actual BBLS]),Null,DSum("Statistic_ActualVol","qry_Master_MarketStats","[Financial_Year_ID]=" & [Financial_Year_ID] & " AND [Month_ID] <=" & [Month_ID] & ""))

OM Gang

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
omgangIT ManagerCommented:
This is the query result after the expression change
OM Gang

Query Result
staceymooreAuthor Commented:
Both of you provided excellent solutions.

However, I am assigning the "Best Solution" to omgang because the solution was directly related to my problem.


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.