CODEWITHSUNDEEP
sqlms-accessms-access-forms
codependent
codependent

Reputation: 24452

Set default value for form field when associated query element doesn't return a value

I have a query that returns the aggregation of the cost of the resources assigned to the company departments every month:

TRANSFORM Sum (Resource.Cost) AS ResourceCost
SELECT Department.Name
FROM (Department INNER JOIN ...
WHERE ...
GROUP BY ...
ORDER BY ...
PIVOT Format([Date],"mmmm");

This would return this:

DEPARTMENT        January February March April May June July
IT                   200     150    200   100  110 800  920
HumanResources       10      150    200   100  110 500  50
Accounting           150      00    10     20  10  0    330

As you can see the values for the next months of this year are empty since those costs aren't returned by the query (they have not been inserted in the data base yet).

Moving on, I have a form that shows this information, including all months of the current year:

DEPARTMENT        January February March April May June July August September October November December
IT                   200     150    200   100  110 800  920  #error #error    #error  #error   #error
HumanResources       10      150    200   100  110 500  50   #error #error    #error  #error   #error
Accounting           150      00    10     20  10  0    330  #error #error    #error  #error   #error

Unfortunately there is an error for those fields where the query doesn't have any value for the associated cost in that month.

The value is set with something like =[August]. Since a valule for August isn't in the result set of the query it shows an error.

I would like to show 0 for all those months without information. I've tried different options such as =Nz([August];0) but they didn't work.

Could you tell me how to set a value of 0 for those fields?

Upvotes: 1

Views: 49

Answers (2)

Parfait
Parfait

Reputation: 107652

Simply specify the field names in PIVOT...IN () clause where non-applicable fields will render as empty columns. You can even use this IN clause to re-order the columns:

TRANSFORM Sum (Resource.Cost) AS ResourceCost
SELECT Department.Name
FROM (Department INNER JOIN ...
WHERE ...
GROUP BY ...
ORDER BY ...
PIVOT Format([Date],"mmmm") IN ('January', 'February', 'March', 
                                'April', 'May', 'June', 
                                'July', 'August', 'September', 
                                'October', 'November', 'December')

Or reversed order for illustration using MonthName + Month functions:

PIVOT MonthName(Month([Date])) IN ('December', 'November', 'October', 
                                   'September', 'August', 'July', 
                                   'June', 'May', 'April', 
                                   'March', 'February', 'January')

Upvotes: 2

Lee Mac
Lee Mac

Reputation: 16015

The reason that =Nz([August],0) doesn't work in this scenario is because it's not that the field [August] is returning null (which would cause 0 to be returned by the Nz function), but rather that the field [August] does not exist in the bound query.

One possible solution is to use conditional aggregation in place of a cross-tab query, e.g.:

select
    department.name,
    sum(iif(month([date])=1,resource.cost,0)) as January,
    sum(iif(month([date])=2,resource.cost,0)) as February,
    ...
from
    department inner join ...
group by 
    department.name

By checking only the month of your [date] field, I am assuming that you are only working with a single year's worth of data.

Upvotes: 0

Related Questions