Using IIF to return a zero value for a null

Hi,

This is not so much a question as a sharing of something I've come
across. It won't be earthshattering nor epiphanous, but it might
prove helpful.

I have some queries of the following form:


SELECT [tipcodes 2003].[event name], Sum([tipcodes 2003]![tipcode
frequency]) AS id017
FROM [tipcodes 2003]
WHERE ((([tipcodes 2003].[tipcode]) In (164,172)))
GROUP BY [tipcodes 2003].[event name];


which will return the sum of the "tipcode frequencies" for "tipcodes"
164 and 172, sorted by "event name". The table "tipcodes 2003" lists
all tipcodes which occurred with a nonzero frequency, i.e. that result
occurred at all, sorted by event name. This works perfectly fine.

However, if I build another query like:


SELECT
[tipcodes 2003].[event name],
recruit101.id101 AS [No Contact],
recruit102.id102 AS [Contacted/Not Eligible],
recruit103.id103 AS [Contacted/Eligible],
recruit104.id104 AS Overquota,
recruit105.id105 AS [Total Recruited],
[No Contact]+[Contacted/Not
Eligible]+[Contacted/Eligible]+[Overquota]+[Total Recruited] AS [Total
Sample]

FROM
((((([tipcodes 2003]
LEFT JOIN recruit101 ON [tipcodes 2003].[event name] =
recruit101.[event name])
LEFT JOIN recruit102 ON [tipcodes 2003].[event name] =
recruit102.[event name])
LEFT JOIN recruit103 ON [tipcodes 2003].[event name] =
recruit103.[event name])
LEFT JOIN recruit104 ON [tipcodes 2003].[event name] =
recruit104.[event name])
LEFT JOIN recruit105 ON [tipcodes 2003].[event name] =
recruit105.[event name])
LEFT JOIN [Total Sample (Recruit)] ON [tipcodes 2003].[event name] =
[Total Sample (Recruit)].[event name]

GROUP BY
[tipcodes 2003].[event name],
recruit101.id101,
recruit102.id102,
recruit103.id103,
recruit104.id104,
recruit105.id105,
[Total Sample];


....I run into an interesting problem. For those events which did not
happen to have any tipcodes in the "Overquota" category, for instance,
there is no record in the "recruit104.id104" query. When the query
above runs into such a situation, it returns a null for that event in
the "Overquota" column. This, in turn, makes the "[Total Sample]"
calculated field return null. As you can imagine, this is something
less than helpful.

There are a few alternatives, each with a varying degree of utility
for the labor:

(1) Rebuild the table(s) to include a reference to each tipcode, and
assign a value of zero to those not included in the original data
file.

(2) Create a query "Total Sample" which sums the appropriate tipcode
frequencies for all events, thereby driving this directly from the
data and not from calculated fields based on other queries. I
actually produced this result, but was a bit bullish about the
calculated field if for no other reason than to serve as a proof of
concept.

(3) Use the following expression:

IIF(IsNull([recruit104]![id104]),0,[recruit104]![id104])

This returns a zero in the "Overquota" column in the above query for
an event name not listed in "id104".


I'm not sure of which method is more practical or more wholesome from
the purist's view. I don't think I need to refer to the tipcodes in
two separate places, though, especially since the sets can get a bit
cumbersome to manage. It seemed better to build the queries at the
lowest level of detail I might eventually want to analyze, and then
simply compile these results with calculations.

Any thoughts?

look in # error in calculation dd 25 may?
"Muraii" wrote in message
m...
Hi,

This is not so much a question as a sharing of something I've come
across. It won't be earthshattering nor epiphanous, but it might
prove helpful.

I have some queries of the following form:


SELECT [tipcodes 2003].[event name], Sum([tipcodes 2003]![tipcode
frequency]) AS id017
FROM [tipcodes 2003]
WHERE ((([tipcodes 2003].[tipcode]) In (164,172)))
GROUP BY [tipcodes 2003].[event name];


which will return the sum of the "tipcode frequencies" for "tipcodes"
164 and 172, sorted by "event name". The table "tipcodes 2003" lists
all tipcodes which occurred with a nonzero frequency, i.e. that result
occurred at all, sorted by event name. This works perfectly fine.

However, if I build another query like:


SELECT
[tipcodes 2003].[event name],
recruit101.id101 AS [No Contact],
recruit102.id102 AS [Contacted/Not Eligible],
recruit103.id103 AS [Contacted/Eligible],
recruit104.id104 AS Overquota,
recruit105.id105 AS [Total Recruited],
[No Contact]+[Contacted/Not
Eligible]+[Contacted/Eligible]+[Overquota]+[Total Recruited] AS [Total
Sample]

FROM
((((([tipcodes 2003]
LEFT JOIN recruit101 ON [tipcodes 2003].[event name] =
recruit101.[event name])
LEFT JOIN recruit102 ON [tipcodes 2003].[event name] =
recruit102.[event name])
LEFT JOIN recruit103 ON [tipcodes 2003].[event name] =
recruit103.[event name])
LEFT JOIN recruit104 ON [tipcodes 2003].[event name] =
recruit104.[event name])
LEFT JOIN recruit105 ON [tipcodes 2003].[event name] =
recruit105.[event name])
LEFT JOIN [Total Sample (Recruit)] ON [tipcodes 2003].[event name] =
[Total Sample (Recruit)].[event name]

GROUP BY
[tipcodes 2003].[event name],
recruit101.id101,
recruit102.id102,
recruit103.id103,
recruit104.id104,
recruit105.id105,
[Total Sample];


...I run into an interesting problem. For those events which did not
happen to have any tipcodes in the "Overquota" category, for instance,
there is no record in the "recruit104.id104" query. When the query
above runs into such a situation, it returns a null for that event in
the "Overquota" column. This, in turn, makes the "[Total Sample]"
calculated field return null. As you can imagine, this is something
less than helpful.

There are a few alternatives, each with a varying degree of utility
for the labor:

(1) Rebuild the table(s) to include a reference to each tipcode, and
assign a value of zero to those not included in the original data
file.

(2) Create a query "Total Sample" which sums the appropriate tipcode
frequencies for all events, thereby driving this directly from the
data and not from calculated fields based on other queries. I
actually produced this result, but was a bit bullish about the
calculated field if for no other reason than to serve as a proof of
concept.

(3) Use the following expression:

IIF(IsNull([recruit104]![id104]),0,[recruit104]![id104])

This returns a zero in the "Overquota" column in the above query for
an event name not listed in "id104".


I'm not sure of which method is more practical or more wholesome from
the purist's view. I don't think I need to refer to the tipcodes in
two separate places, though, especially since the sets can get a bit
cumbersome to manage. It seemed better to build the queries at the
lowest level of detail I might eventually want to analyze, and then
simply compile these results with calculations.

Any thoughts?

Check Access VB Help on the Nz() function which you can use to convert Null
to zero.

I am not aware that you can use Aliases of other Fields in the Calculated
Field [Total Sample]???

--
HTH
Van T. Dinh
MVP (Access)



"Muraii" wrote in message
m...
Hi,

This is not so much a question as a sharing of something I've come
across. It won't be earthshattering nor epiphanous, but it might
prove helpful.

I have some queries of the following form:


SELECT [tipcodes 2003].[event name], Sum([tipcodes 2003]![tipcode
frequency]) AS id017
FROM [tipcodes 2003]
WHERE ((([tipcodes 2003].[tipcode]) In (164,172)))
GROUP BY [tipcodes 2003].[event name];


which will return the sum of the "tipcode frequencies" for "tipcodes"
164 and 172, sorted by "event name". The table "tipcodes 2003" lists
all tipcodes which occurred with a nonzero frequency, i.e. that result
occurred at all, sorted by event name. This works perfectly fine.

However, if I build another query like:


SELECT
[tipcodes 2003].[event name],
recruit101.id101 AS [No Contact],
recruit102.id102 AS [Contacted/Not Eligible],
recruit103.id103 AS [Contacted/Eligible],
recruit104.id104 AS Overquota,
recruit105.id105 AS [Total Recruited],
[No Contact]+[Contacted/Not
Eligible]+[Contacted/Eligible]+[Overquota]+[Total Recruited] AS [Total
Sample]

FROM
((((([tipcodes 2003]
LEFT JOIN recruit101 ON [tipcodes 2003].[event name] =
recruit101.[event name])
LEFT JOIN recruit102 ON [tipcodes 2003].[event name] =
recruit102.[event name])
LEFT JOIN recruit103 ON [tipcodes 2003].[event name] =
recruit103.[event name])
LEFT JOIN recruit104 ON [tipcodes 2003].[event name] =
recruit104.[event name])
LEFT JOIN recruit105 ON [tipcodes 2003].[event name] =
recruit105.[event name])
LEFT JOIN [Total Sample (Recruit)] ON [tipcodes 2003].[event name] =
[Total Sample (Recruit)].[event name]

GROUP BY
[tipcodes 2003].[event name],
recruit101.id101,
recruit102.id102,
recruit103.id103,
recruit104.id104,
recruit105.id105,
[Total Sample];


...I run into an interesting problem. For those events which did not
happen to have any tipcodes in the "Overquota" category, for instance,
there is no record in the "recruit104.id104" query. When the query
above runs into such a situation, it returns a null for that event in
the "Overquota" column. This, in turn, makes the "[Total Sample]"
calculated field return null. As you can imagine, this is something
less than helpful.

There are a few alternatives, each with a varying degree of utility
for the labor:

(1) Rebuild the table(s) to include a reference to each tipcode, and
assign a value of zero to those not included in the original data
file.

(2) Create a query "Total Sample" which sums the appropriate tipcode
frequencies for all events, thereby driving this directly from the
data and not from calculated fields based on other queries. I
actually produced this result, but was a bit bullish about the
calculated field if for no other reason than to serve as a proof of
concept.

(3) Use the following expression:

IIF(IsNull([recruit104]![id104]),0,[recruit104]![id104])

This returns a zero in the "Overquota" column in the above query for
an event name not listed in "id104".


I'm not sure of which method is more practical or more wholesome from
the purist's view. I don't think I need to refer to the tipcodes in
two separate places, though, especially since the sets can get a bit
cumbersome to manage. It seemed better to build the queries at the
lowest level of detail I might eventually want to analyze, and then
simply compile these results with calculations.

Any thoughts?