If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
IFF in Query??
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 |
#2
|
|||
|
|||
IFF in Query??
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 On Feb 15, 10:56 am, HeatherD25 wrote: 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 |
#3
|
|||
|
|||
IFF in Query??
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 .. "HeatherD25" wrote in message ... 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 |
#4
|
|||
|
|||
IFF in Query??
That worked great. Thank you for you explanation -- it was excellent!!
"John Spencer" wrote: 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 .. "HeatherD25" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|