MS Access - prepare a fixed report with variable content

[Swansea Jack]

I would very much appreciate help with a snag I have found. I need to prepare
a report for non-technical users. The report is based on a query that returns
a count for 5 possible 'values' (waiting, order received etc) in a status
field. Sometimes only 4 of the 5 'values' are present - this changes with
time, and according to the status of customers.

If only 4 values are present when I create the report then, sensibly, the
report only contains 4 'values'. If, later, there is an instance of the 5th
'value' the previously created report does not pick up the new 'value', since
the report format is now static. (If I now create a new report it will pick
up all values. However, the report also contains calculations etc that my
users would not be in a position to write for themselves, so I cannot expect
them to create a new report every time they need statistics).

I have created a report with all 5 'values' in it and added my calculations.
The problem here is that if one of the values is not present the report fails
to run and gives a Jet engine field unrecognised error. I have tried a few
permutations to override this, with something like an IsNull or
(Nz[categoryx],0) but have had no success, since as far as the report is
concerned the 'value' is not provided by the underlying query, and therefore
does not exist.

If I put in dummy records to provide all 5 'values' then the statistics are
up the spout.

I would be grateful for any other ideas. As an interim solution I am
importing data into a spreadsheet, which my users are able to manipulate.

Thanks in anticipation.

Swansea Jack

 

[Tom Wickerath]

By chance, is your report based on a crosstab query? This might explain why a
column is not available. In that case, you can use the Column Headings
property to create a fixed heading. In query design view, click on View >
Properties. Select the upper half of the QBE. You should see "Query
Properties" in the blue title bar of the properties dialog (as opposed to
"Field Properties"). Enter a comma separated list of column headings, such
as: "waiting","order received"

If your report is not based on a crosstab query, then I'm having a little
trouble imagining why you are having a problem....you'll need to supply a bit
more information regarding the recordsource of this report.

I have tried a few permutations to override this, with something like
an IsNull or (Nz[categoryx],0) but have had no success...

You might try something like this:
Field: CategoryX1:=Nz([categoryx], 0)

where "CategoryX1" is NOT the name of an existing field. Then use CategoryX1
as the control source for the textbox on your report. You shouldn't have to
do something like this....perhaps the design of your database is not properly
normalized, and is forcing a kludge like this?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I would very much appreciate help with a snag I have found. I need to prepare
a report for non-technical users. The report is based on a query that returns
a count for 5 possible 'values' (waiting, order received etc) in a status
field. Sometimes only 4 of the 5 'values' are present - this changes with
time, and according to the status of customers.

If only 4 values are present when I create the report then, sensibly, the
report only contains 4 'values'. If, later, there is an instance of the 5th
'value' the previously created report does not pick up the new 'value', since
the report format is now static. (If I now create a new report it will pick
up all values. However, the report also contains calculations etc that my
users would not be in a position to write for themselves, so I cannot expect
them to create a new report every time they need statistics).

I have created a report with all 5 'values' in it and added my calculations.
The problem here is that if one of the values is not present the report fails
to run and gives a Jet engine field unrecognised error. I have tried a few
permutations to override this, with something like an IsNull or
(Nz[categoryx],0) but have had no success, since as far as the report is
concerned the 'value' is not provided by the underlying query, and therefore
does not exist.

If I put in dummy records to provide all 5 'values' then the statistics are
up the spout.

I would be grateful for any other ideas. As an interim solution I am
importing data into a spreadsheet, which my users are able to manipulate.

Thanks in anticipation.

Swansea Jack

 

[Swansea Jack]

Thanks Tom. The report is based on a crosstab query, which is why the
presence of the column is dependent on the result from the query.

I'll have a go at your suggestion while I have some follicles left.

Swansea Jack

By chance, is your report based on a crosstab query? This might explain why a
column is not available. In that case, you can use the Column Headings
property to create a fixed heading. In query design view, click on View >
Properties. Select the upper half of the QBE. You should see "Query
Properties" in the blue title bar of the properties dialog (as opposed to
"Field Properties"). Enter a comma separated list of column headings, such
as: "waiting","order received"

If your report is not based on a crosstab query, then I'm having a little
trouble imagining why you are having a problem....you'll need to supply a bit
more information regarding the recordsource of this report.

I have tried a few permutations to override this, with something like
an IsNull or (Nz[categoryx],0) but have had no success...

You might try something like this:
Field: CategoryX1:=Nz([categoryx], 0)

where "CategoryX1" is NOT the name of an existing field. Then use CategoryX1
as the control source for the textbox on your report. You shouldn't have to
do something like this....perhaps the design of your database is not properly
normalized, and is forcing a kludge like this?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I would very much appreciate help with a snag I have found. I need to prepare
a report for non-technical users. The report is based on a query that returns
a count for 5 possible 'values' (waiting, order received etc) in a status
field. Sometimes only 4 of the 5 'values' are present - this changes with
time, and according to the status of customers.

If only 4 values are present when I create the report then, sensibly, the
report only contains 4 'values'. If, later, there is an instance of the 5th
'value' the previously created report does not pick up the new 'value', since
the report format is now static. (If I now create a new report it will pick
up all values. However, the report also contains calculations etc that my
users would not be in a position to write for themselves, so I cannot expect
them to create a new report every time they need statistics).

I have created a report with all 5 'values' in it and added my calculations.
The problem here is that if one of the values is not present the report fails
to run and gives a Jet engine field unrecognised error. I have tried a few
permutations to override this, with something like an IsNull or
(Nz[categoryx],0) but have had no success, since as far as the report is
concerned the 'value' is not provided by the underlying query, and therefore
does not exist.

If I put in dummy records to provide all 5 'values' then the statistics are
up the spout.

I would be grateful for any other ideas. As an interim solution I am
importing data into a spreadsheet, which my users are able to manipulate.

Thanks in anticipation.

Swansea Jack

 

[Swansea Jack]

Hi Tom,

I'll return the wig and hopefully the hair will re-grow. It worked like a
dream - thanks very much.

Swansea Jack

By chance, is your report based on a crosstab query? This might explain why a
column is not available. In that case, you can use the Column Headings
property to create a fixed heading. In query design view, click on View >
Properties. Select the upper half of the QBE. You should see "Query
Properties" in the blue title bar of the properties dialog (as opposed to
"Field Properties"). Enter a comma separated list of column headings, such
as: "waiting","order received"

If your report is not based on a crosstab query, then I'm having a little
trouble imagining why you are having a problem....you'll need to supply a bit
more information regarding the recordsource of this report.

I have tried a few permutations to override this, with something like
an IsNull or (Nz[categoryx],0) but have had no success...

You might try something like this:
Field: CategoryX1:=Nz([categoryx], 0)

where "CategoryX1" is NOT the name of an existing field. Then use CategoryX1
as the control source for the textbox on your report. You shouldn't have to
do something like this....perhaps the design of your database is not properly
normalized, and is forcing a kludge like this?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I would very much appreciate help with a snag I have found. I need to prepare
a report for non-technical users. The report is based on a query that returns
a count for 5 possible 'values' (waiting, order received etc) in a status
field. Sometimes only 4 of the 5 'values' are present - this changes with
time, and according to the status of customers.

If only 4 values are present when I create the report then, sensibly, the
report only contains 4 'values'. If, later, there is an instance of the 5th
'value' the previously created report does not pick up the new 'value', since
the report format is now static. (If I now create a new report it will pick
up all values. However, the report also contains calculations etc that my
users would not be in a position to write for themselves, so I cannot expect
them to create a new report every time they need statistics).

I have created a report with all 5 'values' in it and added my calculations.
The problem here is that if one of the values is not present the report fails
to run and gives a Jet engine field unrecognised error. I have tried a few
permutations to override this, with something like an IsNull or
(Nz[categoryx],0) but have had no success, since as far as the report is
concerned the 'value' is not provided by the underlying query, and therefore
does not exist.

If I put in dummy records to provide all 5 'values' then the statistics are
up the spout.

I would be grateful for any other ideas. As an interim solution I am
importing data into a spreadsheet, which my users are able to manipulate.

Thanks in anticipation.

Swansea Jack

 

[Tom Wickerath]

Hi Jack,

You're very welcome. I'm glad I could be of assistance.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________