IIF isNull isn't providing the output

[DMUM via AccessMonster.com]

Hello

I have created a query that produces a few fields that contain blank spaces.
I want the query to show 0 when the field has no data. I used the following
code:

Defects: IIf(IsNull([dblDefects]),"0",[dblDefects])

I get no errors when I run the query, but the blank fields are not populated
with "0"

I also used: Defects: IIf(IsNull([dblDefects]),0,[dblDefects])

It produces the same output.

I tried isEmpty, no errors, but the fields are still blank

What I am trying to prevent is missing rows when I run the query with a date
criteria. Running the query with no criteria, gives me all the rows,
regardless of the field contents. However, if I run the query I put in date
criteria, it removes any rows that contain no data - thus the need to have
the "0"'s in the blank fields

Any ideas?

Thanks

 

[Steve Schapel]

DMUM,

Whether you put "0" or 0 in the expression depends on the data type,
text or numerical, of the dblDefects field. But, is this the field you
are applying your date criteria to? If not, I can't quite grasp what is
going on here. If you are applying the date criteria to another field
in the query, I can't see how the presence of data in the dblDefects
field could affect the records returned by the query. If, on the other
hand, dblDefects is a date field and this is the field your date
criteria is being applied to, then the approach you are taking won't
solve the problem, as the calculated value still won't match the
criteria. Just shooting in the dark here, since I don't know enough
about what you are doing as yet... but maybe what you need instead is
the criteria to be like this...
<your existing date criteria> Or Is Null

If you still need more help with this, please copy/paste the SQL view of
the query back into your post here, this will help us to understand what
you are doing.

 

[DMUM via AccessMonster.com]

OOps

I made an major error in my explanation. I meant date, because I am creating
a crosstab query where the date is the Column Header but I am actually using
the IIF in the VALUE field, in this case "DEFAULT"

here is the complete query:

TRANSFORM Sum(IIf(IsNull([dblDefects]),0,[dblDefects])) AS Defects
SELECT tblMetricJobsAndControls.intJobControlID, tblMetricJobsAndControls.
JobControlName
FROM qryMetricDates RIGHT JOIN (tblMetricJobsAndControls LEFT JOIN
tblMetricScorecard ON tblMetricJobsAndControls.intJobControlID =
tblMetricScorecard.intJobControlID) ON qryMetricDates.DateStampID =
tblMetricScorecard.MetricMonthStamp
GROUP BY tblMetricJobsAndControls.intJobControlID, tblMetricJobsAndControls.
JobControlName
PIVOT qryMetricDates.MaxOfMetricDate;

Defect is a NUMBER field

Sorry for the confusion

Thanks

DMUM

DMUM,

Whether you put "0" or 0 in the expression depends on the data type,
text or numerical, of the dblDefects field. But, is this the field you
are applying your date criteria to? If not, I can't quite grasp what is
going on here. If you are applying the date criteria to another field
in the query, I can't see how the presence of data in the dblDefects
field could affect the records returned by the query. If, on the other
hand, dblDefects is a date field and this is the field your date
criteria is being applied to, then the approach you are taking won't
solve the problem, as the calculated value still won't match the
criteria. Just shooting in the dark here, since I don't know enough
about what you are doing as yet... but maybe what you need instead is
the criteria to be like this...
<your existing date criteria> Or Is Null

If you still need more help with this, please copy/paste the SQL view of
the query back into your post here, this will help us to understand what
you are doing.

Hello

[quoted text clipped - 22 lines]

Thanks

 

[Steve Schapel]

DMUM,

Try it like this...
TRANSFORM Nz(Sum([dblDefects]),0) AS Defects...