DAO SQL + function = overflow 6 error, sometimes...

[munkee]

Hi all,

I am using a form to basically display monthly and fiscal year to date figures.

I use the following code in the submit button (combo boxes populare year, month, department and incident type):

Private Sub Command89_Click()
On Error GoTo jumpout
 
 
Me.txtCountAccidents = CountingIncidents(cmboMonth, cmboYear, Me.cmboDept, 1)
Me.txtCountSafetyConcerns = CountingIncidents(cmboMonth, cmboYear, Me.cmboDept, 3)
Me.txtRatio = Me.txtCountSafetyConcerns / Me.txtCountAccidents
 
Me.txtFiscalAccidents = FiscalCountingIncidents(Me.cmboMonth, Me.cmboYear, Me.cmboDept, 1)
Me.txtFiscalSafetyConcerns = FiscalCountingIncidents(Me.cmboMonth, Me.cmboYear, Me.cmboDept, 3)
Me.txtFiscalRatio = Me.txtFiscalSafetyConcerns / Me.txtFiscalAccidents
 
completedyo:
Exit Sub
 
jumpout:
MsgBox Err.Description & " - " & Err.Number
Resume completedyo
End Sub

As you can see I call functions to populate the date in the textboxes. The functions are as follows:


First function to count current month stats

'Function used to count the number of incidents per month based on month/year/department and incident type
Public Function CountingIncidents(cmboMonth As String, cmboYear As Integer, cmboDept As Integer, IncidentTyping As Integer)
 
On Error GoTo jumpout
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String
Dim counter As Integer
 
Set db = CurrentDb
 
strsql = "SELECT Count(tblhselog.HSEID) AS CountOfHSEID "
strsql = strsql & "FROM tblhselog "
strsql = strsql & "WHERE (((Format([Incident_date],""mmm""))='" & cmboMonth & "') AND ((Format([Incident_date],""yyyy""))=" & cmboYear & ") AND ((tblhselog.Department)=" & cmboDept & ") AND ((tblhselog.Incident_type)=" & IncidentTyping & "));"
'Debug.Print strsql
Set rs = db.OpenRecordset(strsql, dbOpenSnapshot)
If rs.RecordCount <> 0 Then
counter = rs("CountOfHSEID")
Else
counter = 0
End If
 
'Debug.Print counter
CountingIncidents = counter
 
 
completedyo:
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Exit Function
 
jumpout:
MsgBox Err.Description & " - " & Err.Number
Resume completedyo
 
End Function

Second function to count fiscal year to date figures:

'Function to return the total number of a type of incident in current fiscal year selected
Public Function FiscalCountingIncidents(countMonth As String, countYear As Integer, countDept As Integer, countIncident As Integer)
 
On Error GoTo jumpout
 
Dim dbz As DAO.Database
Dim rsz As DAO.Recordset
Dim strsqlz As String
Dim counterz As Integer
 
Set dbz = CurrentDb
 
strsqlz = "SELECT Count(tblhselog.HSEID) AS CountOfHSEID"
strsqlz = strsqlz & " FROM tblhselog"
strsqlz = strsqlz & " WHERE (((tblhselog.Incident_date) Between fiscalStartDate('" & countMonth & "'," & countYear & ") And fiscalEndDate('" & countMonth & "'," & countYear & ")) AND ((tblhselog.Department)=" & countDept & "))"
strsqlz = strsqlz & " GROUP BY tblhselog.Incident_type"
strsqlz = strsqlz & " HAVING (((tblhselog.Incident_type)=" & countIncident & "));"
 
 
'Debug.Print strsqlz
Set rsz = dbz.OpenRecordset(strsqlz, dbOpenSnapshot)
If rsz.RecordCount <> 0 Then
counterz = rsz("CountOfHSEID")
Else
counterz = 0
End If
 
'Debug.Print counterz
FiscalCountingIncidents = counterz
 
 
completedyo:
rsz.Close
dbz.Close
Set rsz = Nothing
Set dbz = Nothing
Exit Function
 
jumpout:
MsgBox Err.Description & " - " & Err.Number
Resume completedyo
 
End Function

The year to date figures use a function within the sql to calculate the correct fiscal start and end date which for my company is October through to the following September.

Here is an example of one of my functions to calculate the fiscal start date which as I stated sits in my query to use a BETWEEN statement, my end date is similar so no need to spam the thread with that also:

'The following function is used to calculate the fiscal year end date given the month and year
 
Function fiscalEndDate(monthvalue As String, yearvalue As Integer) As String
 
Select Case monthvalue
Case "Jan"
monthvalue = 1
Case "Feb"
monthvalue = 2
Case "Mar"
monthvalue = 3
Case "Apr"
monthvalue = 4
Case "May"
monthvalue = 5
Case "Jun"
monthvalue = 6
Case "Jul"
monthvalue = 7
Case "Aug"
monthvalue = 8
Case "Sep"
monthvalue = 9
Case "Oct"
monthvalue = 10
Case "Nov"
monthvalue = 11
Case "Dec"
monthvalue = 12
Case Else
MsgBox "month value must be supplied"
Exit Function
End Select
 
If (monthvalue >= 10) Then
fiscalEndDate = "30/09/" & yearvalue + 1
Else
fiscalEndDate = "30/09/" & yearvalue
End If
End Function
 

My issue is for some months such as Jan, Feb my code runs perfeclty. However if I select Mar as the month I get "Overflow - 6" error. If I switch the year the error occurs on other months which previously did work.. random it seems..

Any clues what this is? I have googled and not got anything too descriptive back.

[munkee]

Found a partial answer.

It is an issue with the way I have calculated my ratio values (see the first code block).

I dont know exactly why but the calculation must need some boundaries else access is calculating crazily.

Any ideas?

[ADezii]

The method in which you utilize the Function is very unorthodox in that you are modifying one of the Arguments passed to it. Try:

Function fiscalEndDate(monthvalue As String, yearvalue As Integer) As String
Dim bytMonthValue As Byte
 
Select Case monthvalue
  Case "Jan"
    bytMonthValue = 1
  Case "Feb"
    bytMonthValue = 2
  Case "Mar"
    bytMonthValue = 3
  Case "Apr"
    bytMonthValue = 4
  Case "May"
    bytMonthValue = 5
  Case "Jun"
    bytMonthValue = 6
  Case "Jul"
    bytMonthValue = 7
  Case "Aug"
    bytMonthValue = 8
  Case "Sep"
    bytMonthValue = 9
  Case "Oct"
    bytMonthValue = 10
  Case "Nov"
    bytMonthValue = 11
  Case "Dec"
    bytMonthValue = 12
  Case Else
    MsgBox "month value must be supplied"
      Exit Function
End Select
 
If (bytMonthValue >= 10) Then
  fiscalEndDate = "30/09/" & CStr(yearvalue + 1)
Else
  fiscalEndDate = "30/09/" & CStr(yearvalue)
End If
End Function

[munkee]

Thanks for the modification Adezii. Im really weak on function coding to be honest even though they are such a simple concept.

[gershwyn]

Have you looked at what values you are actually calculating? It is very dangerous to divide by a variable without handling for division by zero errors.

Before each division, check to make sure your divisor is valid (non-zero). Decide what value you want to display in case of zero, and provide the ratio value if all is good.

[munkee]

Thanks for the reply gershwyn this is definitly an issue with the ratio calculation and as I said likely overflow due to some infinite calculation.

The ratio is basically the number of safety concerns raised / the number of accidents that have occurred.

If 20 concerns raised and 0 accidents I guess I would want a ratio of 20 however this does mask 20 concerns over 1 accident but that is how the department I am making this for want the calc to run so thats up to them.

My issue may also be with 0 safety concerns and 1 accident etc.

Not a very good measure to be honest but I will bork together some better calc.