IFF in Query??

[Guest]

Hi,

I have a query where I have a lot of calculated fields. I need to run in
monthly to generate a report.

Example of a calculated field:

TOTAL: ([Value1]+[Value2]+[Value3])

Some months I will have all 3 values to calculate, but some months I will
only have 1 or 2 or the values to calculate. These values come from a
crosstab query so if the values aren't there that month I will get an error
when I try to run the query.

Is there a way to write the calculation such that I can say "If Value1 is in
the cross tab query, add if to Value 2 (if it's in the crosstab query), add
it to Value 3 (if it's in the crosstab query).

Hope this makes sense. Thanks for your help!!

Heather

 

[Jason Lepack]

The Nz Function returns a value if the value passed to it is null.

Nz([fieldWithPossibleNull],valueToReturnIfNull)

TOTAL: (Nz([Value1],0)+Nz([Value2],0)+Nz([Value3],0))

Cheers,
Jason Lepack

 

[John Spencer]

The way to fix this problem is to change the crosstab so it returns the
values everytime.

In the crosstab query you can specify the field name(s) using an In clause
in the PIVOT statement.

TRANSFORM ...
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
PIVOT [FieldName] In ("InProgress","OnTime", "Late","VeryLate")

In the query grid, you do this by
-- Select View properties
-- Click on the grey area above the grid, so you are looking at the
query's properties
-- Inputting your values in Column Headings separated by commas (or
semicolons if your separator is semi-colons)

When you do this the specified cross-tab columns will show up and ONLY those
crosstab columns will be visible. If you mistype a value, you will get a
column with that name and no data (all nulls) in that column.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

That worked great. Thank you for you explanation -- it was excellent!!

The way to fix this problem is to change the crosstab so it returns the
values everytime.

In the crosstab query you can specify the field name(s) using an In clause
in the PIVOT statement.

TRANSFORM ...
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
PIVOT [FieldName] In ("InProgress","OnTime", "Late","VeryLate")

In the query grid, you do this by
-- Select View properties
-- Click on the grey area above the grid, so you are looking at the
query's properties
-- Inputting your values in Column Headings separated by commas (or
semicolons if your separator is semi-colons)

When you do this the specified cross-tab columns will show up and ONLY those
crosstab columns will be visible. If you mistype a value, you will get a
column with that name and no data (all nulls) in that column.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi,

I have a query where I have a lot of calculated fields. I need to run in
monthly to generate a report.

Example of a calculated field:

TOTAL: ([Value1]+[Value2]+[Value3])

Some months I will have all 3 values to calculate, but some months I will
only have 1 or 2 or the values to calculate. These values come from a
crosstab query so if the values aren't there that month I will get an
error
when I try to run the query.

Is there a way to write the calculation such that I can say "If Value1 is
in
the cross tab query, add if to Value 2 (if it's in the crosstab query),
add
it to Value 3 (if it's in the crosstab query).

Hope this makes sense. Thanks for your help!!

Heather