Handling Null Values in MS Acess Query

Posted on 2013-09-12
Medium Priority
Last Modified: 2013-09-12

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.


Question by:staceymoore
  • 2
LVL 41

Assisted Solution

als315 earned 500 total points
ID: 39487973
test: IIf(IsNull([Statistic_ActualVol]),0,1)
There is no field with name CY Actual BBLS in your sample, but there is Statistic_ActualVol
LVL 28

Accepted Solution

omgang earned 500 total points
ID: 39487983
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
LVL 28

Expert Comment

ID: 39487991
This is the query result after the expression change
OM Gang

Query Result

Author Closing Comment

ID: 39488026
Both of you provided excellent solutions.

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



Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

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

When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question