What I do is have an SQL statement created according to the criteria
selected by the user. The code to create the SQL is as follows:
lstr_SQL = "SELECT [Survey Results].Neighborhood, [Survey Results].[" &
lstr_Demographics & "] AS Demographic, "
lstr_SQL = lstr_SQL & "[Survey Results].[" & lstr_Topics & "] AS Topic INTO
[Demographic / Topic] "
lstr_SQL = lstr_SQL & "FROM [Survey Results] "
lstr_SQL = lstr_SQL & "WHERE [Survey Results].[" & lstr_Topics & "] <> ''
AND [Survey Results].[" & lstr_Demographics & "] <> '' "
lstr_SQL = lstr_SQL & "AND [Survey Results].Neighborhood = '" &
Neighborhoods.Value & "';"
and turns it into an SQL statement like the following:
SELECT [Survey Results].Neighborhood, [Survey Results].[Years Lived] AS
Demographic, [Survey Results].[Traffic Problems] AS Topic INTO [Demographic
/ Topic]
FROM [Survey Results] "
WHERE [Survey Results].[Traffic Problems] <> '' AND [Survey Results].[Years
Lived] <> '' AND [Survey Results].Neighborhood = 'Annandale';
The chart is then based off of the table using the static RowSource I posted
in the previous post. If the user changes the criteria for the chart, the
table is recreated, but the names of the fields stay the same, thus keeping
a static RowSource. The only thing not working right is the labeling of the
X-Axis.
- Merg
--
Today's problems don't worry me,
I haven't solved yesterday's yet.
Duane Hookom said:
I don't see anything like "Years Lived" in the Row Source.
You can always change the SQL property of a query that is the Row Source
of
a chart control.
There are times when Access is just too helpfull with conversions to dates
and numbers. You may need to add an underscore or period or similar to a
value/expression.
--
Duane Hookom
Microsoft Access MVP
The Merg said:
The RowSource being used for the chart is as follows:
TRANSFORM Count([Topic]) AS [CountOfTopic] SELECT [Demographic] FROM
[Demographic / Topic] GROUP BY [Demographic] PIVOT [Neighborhood];
I cannot pass an SQL statement to the RowSource property as it is a chart
and Access does not allow dynamic updating of the RowSource for a chart.
Instead I have it based off of a table and dynamically change the data in
the table using an Append Query SQL statement depending on what criteria
the
user selects.
The strange labeling only occurs with those specific entries I mentioned
in
the previous post. Even though the field is defined as text, if the Axis
is
labeled to be formatted as General, it converts the text to a date if
possible. If I change the formatting to be Text, it changes those same
labels to the integer.
- Merg
--
Today's problems don't worry me,
I haven't solved yesterday's yet.
By default tables have no order of records. Change the Row Source to a
query
or SQL statement and then provide it.
--
Duane Hookom
Microsoft Access MVP
:
The chart is created directly from a table. The fields in the table
are
all
typed as Text fields.
Thanks,
Merg
--
Today's problems don't worry me,
I haven't solved yesterday's yet.
Can you provide the Row Source property with data types?
--
Duane Hookom
Microsoft Access MVP
:
I have a chart in an report, but the X-Axis is not being labeled
correctly.
The data in the field that is being used for the X-Axis is "Years
Lived"
and
are year ranges with the data being:
0-1
1-5
6-10
11-20
Over 20
The "Over 20" value is the only one that will display correctly. If
I
have
the X-Axis format set to General, it takes the "1-5", "6-10", and
"11-20"
labels and converts them to dates. If I set the format to Text, it
converts
them to the integers "40137", "39818", and "39974".
Does anyone have any ideas on what is happening here?
Thanks,
Merg
