Scatter-Chart

[RolfHeller]

I want t draw a scatter-chart of counted gas-quantities of a specific gas-meter (14) at irregular time-intervals by the following code:

Private Sub Bef_Grafik_Click() Dim myChart As BAC_Chart Set myChart = BAC.Chart(Me.ctlWebbrowser) 'myChart.DrawChart 'myChart.DataSource.ObjectName = "tbl_ZStand" myChart.DataSource.SQLStatement = "Select Z_ID, Z_S_Datum, Z_S_Stand from tbl_ZStand where Z_ID = 14 order by Z_S_Datum asc" myChart.DataSource.DataFieldNames = Array("Z_S_Datum", "Z_S_Stand") myChart.DataSource.LabelFieldName = "Z_ID" myChart.ChartType = chChartType.Scatter myChart.DrawChart End Sub

I get the message: 9 Index außerhalb des gültigen Bereichs.

Sciptfehler dem Skript auf dieser Seite ist ein Fehler aufgetreten. Zeile: 24 Zeichen: 29 Fehler: '}' erwartet Code: 0 URL: file:///C:/Users/Heller/AppData/Local/Temp/BetterAccessChart _ctlWebbrowser.html Möchten Sie, dass Scripts auf dieser Seite weiterhin ausgeführt werden? Ja Nein S How can I improve the reaction?

[team-moeller]

Hi @RolfHeller ! Can you provide a sample database with some demo data to reproduce the problem? I would love to have a deeper look at this.

[RolfHeller]

Hi, @@.***> ! Appended you will find the demo access file (despite the xlsx-extension) with included: tbl_ZStand and a form called: frm_ZStand, that produces the reaction mentioned in GitHub fort he scatter-graph.

You will get the same result in your original sample data when selecting scatter and sql-statement.

I see the difficulties, that the x-axes (Z_S_Datum) is a date-field and the field for the category (Z_ID) is numeric.

I'm expecting a graph as it can be produced in EXCEL (see the second append)

I'm waiting for your deeper look at it. Better-Access-Charts-Heller.xlsx Grafik-Zählerstände.xlsx

[team-moeller]

Hi @RolfHeller ! I had a first look. The recordcount is not always right so we have to do a rst.movelast to get the right number. This will fix the error "9 Index außerhalb des gültigen Bereichs." I will bring in this fix in the next release. Related to the scatter chart we have a problem now when the expression for the x-axis is a date. In the short run I do not see a simple solution here. Maybe a simple line chart will be an alternative for your needs.

[RolfHeller]

Will line chart work with date (irregular time intervalls) as x-axis? In your examples line chart seems to work with self estabished regular categories only.

[team-moeller]

Have you tried it by yourself? ;-)

[team-moeller]

OK, I had another look into your database. First I imported all modules from the latest release. So the error "9 Index außerhalb des gültigen Bereichs" does not happen anymore. Then I changed your code like this:

Dim myChart As BAC_Chart
Set myChart = BAC.Chart(Me.ctlWebbrowser)
myChart.DataSource.SQLStatement = "Select Z_ID, Z_S_Datum, Z_S_Stand from tbl_ZStand where Z_ID = 2 order by Z_S_Datum asc"
myChart.DataSource.DataFieldNames = Array("Z_S_Stand")
myChart.DataSource.LabelFieldName = "Z_S_Datum"
myChart.ChartType = chChartType.Line
myChart.DrawChart

This created a linechart.

[team-moeller]

Conclusion:

• the technical error has gone with the latest release
• Related to the scatter chart we have a problem now when the expression for the x-axis is a date. We won't fix that.
• A linechart can be an alternative => I will close this issue.

[RolfHeller]

The solution you suggested does give a line chart with regular time intervals although the data in the database are not evenly distributed along the x-axis. Imo the representation of the data is misleading.

Perhaps you could use the scatter-chart with a conversion of time-data (on the x-axis) as decimal data (as EXCEL does). The difficulty remains to generate a x-axis with appropriate labels.

Thanks. Rolf