Devide X-axis graph (according to the data the graph is based on). (1 Viewer)

[Sniper-BoOyA-]

Good morning,

Ive got a graph on a report. At this point, the scale of the graph is set to 'Auto' . Which works, but the graph-line is not in the centre of the graph.

Due to the fact that we have to send these graphs to our customers we would like the graph to be presented perfectly.

What i was wonder is:

Is it possible to set the scale of an axis, in this case X-axis, according to the data which the graph is based on?

Ive got a data set, with the following data.

X axis .. Y-Axis
----------------
8.1,,,,,,,,,1834
8.7,,,,,,,,,,1867
11.8,,,,,,,,,1857
13,3 ,,,,,,,,,1795

What i would like to do is the X-axis to start 2 'ticks' before and end 2 'ticks' after the actual data.

So i would like to start the X-Axis at 6, and end the X-axis at 15.

This can be done manually by setting the minimum and maximum scale, which works in this case.

But the next project can have a totally different data set.

Any ideas?

Cheers!

 

[ChrisO]

This works for the numbers given:-

Private Sub Detail_Format(ByRef intCancel As Integer, _
                          ByRef intFormatCount As Integer)
                          
    Dim sngMinRange As Single
    Dim sngMaxRange As Single
    
    Const conX_Axis    As Long = 1
    Const conAxisName  As String = "X_Axis"
    Const conChartName As String = "chtTestChart"
    
    If intFormatCount = 1 Then
        With Me(conChartName)
            sngMinRange = DMin(conAxisName, .RowSource)
            sngMaxRange = DMax(conAxisName, .RowSource)
        
            With .Axes(conX_Axis)
                .MajorUnit = Int((sngMaxRange - sngMinRange) / 5)
                .MinimumScale = Int(sngMinRange - .MajorUnit * 2)
                .MaximumScale = Int(sngMaxRange + .MajorUnit * 2)
                .CrossesAt = .MinimumScale
            End With
        End With
    End If

End Sub

Be careful if the scale span drops below 1.

Edit:
I guess you might as well have the test database.

Chris.

 

[Sniper-BoOyA-]

Cheers! Looks like it worked. I will test it with a different data set aswell.

Thanks again for your input!

 

[Sniper-BoOyA-]

What if my graph is based on a query.

Will i still be able to use

sngMinRange = DMin(conAxisName, .RowSource)
sngMaxRange = DMax(conAxisName, .RowSource)

?

 

[Sniper-BoOyA-]

Hmm, now the graph (subreport) wont even load anymore when using :

Option Compare Database
Option Explicit

Private Sub Details_Format(ByRef intCancel As Integer, _
                          ByRef intFormatCount As Integer)
                          
    Dim sngMinRange As Single
    Dim sngMaxRange As Single
    
    Const conX_Axis    As Long = 1
    Const conAxisName  As String = "X_Axis"
    Const conChartName As String = "GraphName"
    
    If intFormatCount = 1 Then
        With Me(conChartName)
            sngMinRange = DMin(conAxisName, .RowSource)
            sngMaxRange = DMax(conAxisName, .RowSource)
        
            With .Axes(conX_Axis)
                .MajorUnit = Int((sngMaxRange - sngMinRange) / 5)
                .MinimumScale = Int(sngMinRange - .MajorUnit * 2)
                .MaximumScale = Int(sngMaxRange + .MajorUnit * 2)
                .CrossesAt = .MinimumScale
            End With
        End With
    End If
End Sub

Any ideas why?? According to the VBA compiler the code should work. Its pretty much the same as yours afterall.

The only difference between my graph and urs is that the source of the graph isnt the table but a query:

SELECT [watergehproct],Sum([drgdichtheidproct]) AS [SomVandrgdichtheidproct] FROM [ResultaatProctor] GROUP BY [watergehproct];

 

[ChrisO]

It would help me help you if you could post a small demo using your table and field names.
Just the report with the graph and the table with some data should do.

Access 2003 please.

Chris.

 

[Sniper-BoOyA-]

Good Afternoon Chris,

I hope this what you are looking for.

I copied my subreport (with needed queries and tables)

into ur test.mdb.

 

[ChrisO]

Two new queries:

qrySubReportChart
qrySubReportChartByID

The charts Row Source is set to qrySubReportChartByID.

In the Details OnFormat event qrySubReportChartByID is filtered by proctorID.
The SQL is written to qrySubReportChartByID and the Chart is Requeried:-

Private Sub Details_Format(ByRef intCancel As Integer, _
                           ByRef intFormatCount As Integer)
                           
    Dim sngMinRange As Single
    Dim sngMaxRange As Single
    
    Const conX_Axis    As Long = 1
    Const conAxisName  As String = "watergehproct"
    Const conChartName As String = "grafiek"
    
    CurrentDb.QueryDefs("qrySubReportChartByID").SQL = ChartSQL(proctorID)
    
    With Me(conChartName)
        .Requery
    
        sngMinRange = DMin(conAxisName, .RowSource)
        sngMaxRange = DMax(conAxisName, .RowSource)

        With .Axes(conX_Axis)
            .MajorUnit = Int((sngMaxRange - sngMinRange) / 5)
            .MinimumScale = Int(sngMinRange - .MajorUnit * 1)
            .MaximumScale = Int(sngMaxRange + .MajorUnit * 2)
            .CrossesAt = .MinimumScale
        End With
    End With

End Sub


Private Function ChartSQL(ByVal lngID As Long) As String

    ChartSQL = " SELECT watergehproct," & _
               "        SomVandrgdichtheidproct" & _
               " FROM qrySubReportChart" & _
               " WHERE proctorID = " & lngID

End Function

Chris.

 

[Sniper-BoOyA-]

Oh snap. You nailed it.

Thanks Chris for all the time and effort you put into this!

 

[Sniper-BoOyA-]

Chris, first of all i would like to thank you for your help. The graphs are looking way better now.

Ive got one question though;

What if i have 3-4 different ProctorID's?? Ive noticed that the graph will be generated just fine for ProctorID 1 and 2. But when there are 3, the third graph is blank.

Ive been looking at the code, but i just cant figure it out.

 

[ChrisO]

I guess the problem is with the data and the table lookups don’t help.

But I managed to get 4 graphs working, see attached.

Chris.

 

[carlton]

This works for the numbers given:-

Private Sub Detail_Format(ByRef intCancel As Integer, _
                          ByRef intFormatCount As Integer)
                         
    Dim sngMinRange As Single
    Dim sngMaxRange As Single
   
    Const conX_Axis    As Long = 1
    Const conAxisName  As String = "X_Axis"
    Const conChartName As String = "chtTestChart"
   
    If intFormatCount = 1 Then
        With Me(conChartName)
            sngMinRange = DMin(conAxisName, .RowSource)
            sngMaxRange = DMax(conAxisName, .RowSource)
       
            With .Axes(conX_Axis)
                .MajorUnit = Int((sngMaxRange - sngMinRange) / 5)
                .MinimumScale = Int(sngMinRange - .MajorUnit * 2)
                .MaximumScale = Int(sngMaxRange + .MajorUnit * 2)
                .CrossesAt = .MinimumScale
            End With
        End With
    End If

End Sub

Be careful if the scale span drops below 1.

Edit:
I guess you might as well have the test database.

Chris.

I'm trying to solve the same problem. I found the .Crosses and .CrosseAt properties but both give run time error '438'. Object doesn't support this property or method