monthly report problem; missing fields threated as parameters

[JeroenNL]

Hi there,

I run daily queries which summarize some data for me and put totals in a table (using maketable queries). Now I want to create a month-report but I encounter a problem.

The month report needs to show the totals for all days of the month (so from 1 to 31). But now suppose I run the queries on day 1 to day 21. I don't have the rest of the month data now so I can't show the report. It will ask for the missing fields as if they are parameters. Is there a way to solve this problem?

Bye,
Jeroen

 

[PaulBricker]

Is this a crosstab query by some chance. You might be able to solve the problem by using the Nz function. In the control source for you fields you can put
=Nz([FieldName],0)
This should put a value of zero in for any fields missing. If it's a crosstab, you my have to rename the Textboxes if you used the Report wizard. For example if your textbox is named 22 and your control source is 22 then you need to delete that textbox, add a new one in it's place, leave it named whatever Access names it (usually text101 or whatever) and then set the control source to =Nz([22],0).

Post back if I'm way off the mark.

Paul

 

[JeroenNL]

Thanks for your reply. I'm indeed working with a crosstab query. However, the problem is that fields are really missing from the crosstab query. So for example:

=Nz([22],0)

doesn't work if the field 22 isn't in the crosstab query. It will then ask for the value of 22 as if it was a parameter. I don't understand what you mean with changing the textbox names, why should that be done?

To summarize my problem: I have data for days 1 to 21, but need to show days 1 to 31. The crosstab query can only select days 1 to 21 because that's the only data I have stored in my table (when I run the query on day 21). However, the monthstatus should always show days 1 to 31. The missing days should be zeros.

Bye,
Jeroen

 

[PaulBricker]

I would say that you need to create a table that has a record or records that cover all 31 days and then join that table with your normal table in a query and then use the query for your crosstab. If you don't have the fields in the table you are using, there's no way to get them into the crosstab. By creating a bogus record that has all 31 days in it (or 31 records, one for each day) you may be able to get around that. I don't know enough about your data to be sure but it's a place to start.
The reason you may need to change the names is that crosstabs that have null fields just don't allow you to use the Nz() function to insert 0's when the name of the field is the same as the Control Source. I don't know why, I just know that that is what we had to do under similar circumstances.

Paul

 

[JeroenNL]

Thanks again Paul.

Funny, I came to the same conclusion before reading your reply. I have created a bogus table which covers all my days (so up to 31). The data for those days are all zeros.
First I select all days (including the data) of which I actually have data from my actual data table. I then select the remaining days from the bogus table. Then I use a Union query to put the data together. Finally I use a crosstab query to create a nice monthstatus report. Works perfectly in my testdatabase, now all I have to do is implement it in my actual application. Thanks again!