Crosstab query and report

[dendic]

I have a crosstab query one field has three options "Negative","Postive" and "Pending". The problem is on my report I need to show the total of "positive" but if there are no records with a "positive" then the field doesn't exist in the query and I can't show the result as zero on my report. Example: If there are no "positives" My results are Total Records, Total of Pending and Total of Negative but there is no field for "positives" that would be OK if on my report I could show zero in the positive field. If could format my report to recognize there is no field "positive" I could populate a text box to zero. If there is a "positive field then display the result of that field.

 

[Melagan]

I think the NZ function is what you're looking for. It will return a value for a variant if that variant is null:

Nz(variant[, valueifnull])

So maybe you could do something like:

optPositive: nz([positive_field])

That should return a zero value for all [positive_field]'s that are currently null. This is assuming [positive_field] is numeric; if it is a string, then it will return a zero-length string, or "".



~Melagan
______
"It's never too late to become what you might have been.

 

[dhookom]

Set the Column Headings property of the crosstab query to:

Column Headings: "Negative","Postive","Pending"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]