report from cross tab

[yessir]

I have a report from a cross tab query.

it is selecting "EN-02", "EN-04", "EN-05" as a value.

The problem occurs because there is no control source for a value in the crosstab.

The problem is that if the query returns and there is no value for "EN-02" then the report fails and say that it can't recognize "EN-02" as a valid field name or expression in the report because there is no value in the query for it.

How can I get the report to load the returned values no matter what is returned from the query?

ie if there is no control source, set the value to say "No flow"

PS: This is urgent, I am posting at 9AM and i REALLY need to get this working before 12Noon...

Please Help
~

 

[KenHigg]

Did you try saving the crosstab query and then basing a new regular select query on it, using the nz stuff?

ken

 

[yessir]

yes, but it still only selects based on what is in the query, and if there are no values in 02, 04 or 05 then there is nothing to select and thus it won't work.

unless you know a way to get it to...

 

[KenHigg]

Can you get enough of it in a db to post?

ken

 

[yessir]

I imported itinto hte db1, attached here

 

[KenHigg]

Let's see - EN-02, EN-05 are outlet identifiers. Right. And you get samples then save the sample data by outlet id and date (among other things). And you want your report to do what? (forget the crosstab query for a minute)...

ken

 

[yessir]

I want it to look like the detail part of the crosstab query

as in
date 02flowvalue 04flowvalue 05flowvalue

where the critera are met in the qry_RPT_Glycol

 

[yessir]

the values are for a month of a year

so there will be 4-5 rows

each row should show the date and all values for 02 04 05 of that week.

since 0 is a valid entry, if it is blank i want it to say "No flow"

 

[KenHigg]

Can you do an update query to put '0' where nulls exist in your primary table?

ken

 

[yessir]

no because both 0 and null are valid entries

as 0 flow denotes that there was liquid, and null denotes an absebnce thereof

 

[yessir]

unfortunatley, i have to leave my work place now.

if you stumble across a solution, that's great and THANKYOU

but i won;t be able to see it till tomorrow

thanks for allyour help thus far