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

[jkoopmann]

nulls are not a value and do not compare to anything except the expression IS NULL.

if you are trying to compare <, >, or = to the date you are entering and want to include the null rows then you will have to include OR column IS NULL to your query.

[xeb]

This is what my table looks like:

Date     F1  F2  F3  F4  F5  F6  F7 

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

As you can see some of the fields (F) are blank. When I run my report, which averages the records, Access just ignores the blank fields.

However, when I run my parameter query (which is the Record Source for the report) on a single date, no data is returned. However, if I run more than one date it returns correctly.

Thanks for your help.

[skhanal]

Post your query.

Do you use BETWEEN or < > in your WHERE clause?

[xeb]

Hi:

I don't see how a different query statement will help me. My two reports contain up to ten expressions that are calculating on different fields given my needs. (It works fine in all cases except when using a single date parameter query.)

Wouldn't I need a bunch of queries to accomplish what the report does when I run it?

As I said before, the parameter query works fine under the following circumstances:

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 does not work when you select a single date for a single record that has one or more blank fields.

I've tested and retested it many times, and I'm sure that it is only the single date, single record parameter query that will not work. In all other cases, the report calculations are correct.

It seems that there must be something wrong that can be fixed without changing how I'm doing everything else. In other words, the opinion of an inexperienced Access person like myself is that most of it isn't broken, so why change everything.

As I'm sure you already know, I think I found out that the problem is in the calculations because when I run the parameter query for a single date, single record it does return the correct data for that date as long as it is not calculated.

Thanks,

xeb