Microsoft
I think I understand the issue along with usable workaround.
However, I do think it would be nicer if it just worked as expected 😊
Issue:
l When using Parameters that are variable(s) and are selected by the user from a form, if a cross tab query is anywhere in the chain of calculations to get to the final data, it cannot be displayed on a Form or Report. (The Report aspect has not been tested, but assuming it would be the same issue.)
Ø If one tries to display a table in a Subform or such, the table will not display
Ø If one tries to use the new (2018-2019) charting capability and selects a “Legend (Series)”, the chart will not display
l The underlying problem is that when there is a crosstab query with a parameter, the set of columns cannot be determined without knowing the value of the parameter.
Ø Without knowing what the output columns are it’s not possible to generate the table or chart.
Ø FYI – understanding that the flexibility is very limited, but the old charting functionality can handle it.
Workaround:
l Once the final data / query is obtained the data can be written to a temporary table for displaying and graphing.
Ø This requires quite a bit of coding to assure that the graph & table are always in sync with what the user has selected.
Ø However, it does seem to give the final result of being able to fully use the updated (2018 – 2019) charting capability added to Access.
From your comments, it sounds like the issue is known.
If you would like for me to create a database that creates the phenomenon, let me know.
Otherwise, I’m assuming it’s not needed.
Related but different question on the formating in the new charts
When I click on the chart, select "Properties" / "Format" I found selections for:
(1) "PrimaryValues Axis Format"
(2) "Primary Vlaues Asix Display Units"
(-) However, I can find anything that lets me set the "Decimal Places".
Does a setting for Decimal Places for Axis Format exist, and I'm just missing it?
If / when the new (2018-2019) charting functionality is updated to handle CrossTab with Parameters, please let me (and others) know.
Thank you again for your time and explanations.
BrianR
