Problem with Parameter Query for Report

[xeb]

I just finished eight hours of work on my database and I've got a problem.

I have a report based on a table with a date field and quite a few other fields. The report produces averages on all the records in the table just fine.

In order to allow my users to select a date or date range for the records they desire, I added two parameter queries that use dates. The queries prompt the user to either "Enter Date:" or "Enter Start Date:" and "Enter End Date:".

It works fine except when you enter a single date for a field that has one or more null values. However, if you query for dates that include both records with and without null values, it returns the correct calculations.

Why is it doing this? How can I correct it?

Thanks

 

[tracy1234]

Look into using the NZ function to solve your problem. Syntax is:

Nz ( variant, [ value_if_null ] )

ex: Nz(my_column_1,[0])- Nz(my_column_2,[0])

This will eliminate any errors that are caused by trying to do calculations with nulls.

Alternates are to establish a default value of zero for your possibly null columns or run an update on your table to replace the nulls with zeroes.

 

[xeb]

Where does "Nz" go? Do I use it in the report expressions?
I tried it in a report expression and instead of the output being blank it was zeros so it did something.

The epxressions in my report are like this:

=(Sum([1])+Sum([2])+Sum([3]))/(Count([1])+Count([2])+Count([3]))

Where would "Nz" go in this expression.

Also, I found out that I think the problem is in the calculations because when I run the parameter query for a single date it does return the correct data for that date as long as it is not calculated.

 

[tracy1234]

What datatypes are [1], [2], and [3]? What is the default value in the table design for these fields?

Can you clarify these two statements?

"It works fine except when you enter a single date for a field that has one or more null values. However, if you query for dates that include both records with and without null values, it returns the correct calculations."

"Also, I found out that I think the problem is in the calculations because when I run the parameter query for a single date it does return the correct data for that date as long as it is not calculated."

The nz function replaces nulls with zeroes (or whatever you specify) so you seeing zeroes instead of blanks sounds right.

Can you give a simple example of your data and the desired results in the report?

-Tracy

 

[tracy1234]

Here's a small example for you:

Table1
Fields 1,2,3,Date
Datatypes for 1,2,3 are number with default left at null
------------------------------------
1     2      3       Date
------------------------------------
1                    1/6/2004
      2              1/6/2004
1     2              1/6/2004

If you calculate an average, you need to take into account that [3] has null values:

Avg =(Sum([1])+Sum([2])+NZ(Sum([3])))/(Count([1])+Count([2])+NZ(Count([3])))

I hope this helps you to understand the nz function.

 

[xeb]

Sorry, but what are datatypes?

The default value in the form that I use for data entry is 3. I don't think I have a default value in the table.

It works fine in these cases:

1. You select a date range that includes all records. Even the records with blank fields are calculated fine.

2. You select a date range for as few as two records and the calculations work fine even if one of the two records has blank fields.

3. You select a date for a record with no blank fields.

It just won't work for a single date (single record) with one or more nulls.

I have a report that simply displays the value of a given field, like [1]. No calculation is done. In this case, the single date (single record) parameter query returns the correct value, which leads me to believe that the problem is with the calculations.

For me, the problem I think I'll have with the "Nz" function is that it will replace all the nulls with "0" which will cause my averages to be wrong. Is there something we can replace them with that won't interfere with the calculation results?

My table looks like this:

Date     1    2    3    4    5    6    7 (Etc.)

1/5/04   1    2    3    1    2    3    1
1/6/04             1    2    3    1    2    
1/7/04   1    2    3    1    2    3    1

If I run my parameter query on all the records (1/5/04, 1/6/04, and 1/7/04) it returns the correct calculation data despite the presence of two nulls.

If I run just 1/5/04 or 1/7/04 it works fine.

However, If I run 1/6/04 it comes back with blank calculation data, apparently because of the nulls.

I've been using this database for quite a while with success. Reports are generated for all records in the table, and that was just fine then.

Now, I need to be able to generate reports for either one date or a date range.

I hope this is the information you need to help me.

Thanks,

xeb

 

[tracy1234]

xeb -

If you look at your table in design view, you'll see that each field for the table has a given data type. Examples are Text, Number, Date/Time. I'll take your example literally and assume that you are using Numbers.

Your blank calculation for 1/6/2004 is because of the null values. The expression that you are using can't evaluate the null values. You need to use the NZ function if you want your results to not be skewed by entering zeroes instead of nulls.

If you have this:

[1]    [2]     [3]    Date
-----------------------------
                3    1/6/2004

and you want to ignore the nulls (instead of counting them as zeroes) then use this:

=((NZ(Sum([1]))+NZ(Sum([2]))+NZ(Sum([3])))/(Count([1])+Count([2])+Count([3])))

This will result in an average of 3 for the day.

If however, you want the nulls to be zeroes, the easiest thing to do is to default them to zeroes in the table. With zeroes, your average is 1. In certain situations, I can see how either answer might be desired.

-Tracy

 

[xeb]

Hi:

Thanks for answering.

I will try the expression you provided.

All the knowledge bases say use the "Nz" function, but it seems that all that will do is assign a value of "0" to my nulls, which will make my averages incorrect.

However, your use of "Nz" looks different that anything I've seem.

As you indicated, I must ignore the nulls (instead of counting them as zeroes), otherwise the average will be wrong.

Thanks,

xeb

 

[xeb]

Hi:

This is what is apparently working:

=((NZ(Sum([T1]))+NZ(Sum([T2]))+NZ(Sum([T3])))/(Count([T1])+Count([T2])+Count([T3])))

Although "NZ" assigns the value of "0" to a null it apparently does not use that "0" when it does a calculation. Is that right?

Thanks,

xeb

 

[tracy1234]

xeb - nz either returns a zero or a zero length string if no value is specified. Because we are working with numbers, it returns a zero.

So we have:

0+0+3 for the sum part of the equation.

Because the count function does not see a null as a value, the count part of the equation evaluates to 1.

3/1 = 3

-Tracy

 

[xeb]

Tracy:

Thanks, I understand it now.

I really appreciate your patience.

Thanks,

xeb