469,150 Members | 1,968 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,150 developers. It's quick & easy.

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

374 256MB
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):

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command89_Click()
  2. On Error GoTo jumpout
  3.  
  4.  
  5. Me.txtCountAccidents = CountingIncidents(cmboMonth, cmboYear, Me.cmboDept, 1)
  6. Me.txtCountSafetyConcerns = CountingIncidents(cmboMonth, cmboYear, Me.cmboDept, 3)
  7. Me.txtRatio = Me.txtCountSafetyConcerns / Me.txtCountAccidents
  8.  
  9. Me.txtFiscalAccidents = FiscalCountingIncidents(Me.cmboMonth, Me.cmboYear, Me.cmboDept, 1)
  10. Me.txtFiscalSafetyConcerns = FiscalCountingIncidents(Me.cmboMonth, Me.cmboYear, Me.cmboDept, 3)
  11. Me.txtFiscalRatio = Me.txtFiscalSafetyConcerns / Me.txtFiscalAccidents
  12.  
  13. completedyo:
  14. Exit Sub
  15.  
  16. jumpout:
  17. MsgBox Err.Description & " - " & Err.Number
  18. Resume completedyo
  19. 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
Expand|Select|Wrap|Line Numbers
  1. 'Function used to count the number of incidents per month based on month/year/department and incident type
  2. Public Function CountingIncidents(cmboMonth As String, cmboYear As Integer, cmboDept As Integer, IncidentTyping As Integer)
  3.  
  4. On Error GoTo jumpout
  5.  
  6. Dim db As DAO.Database
  7. Dim rs As DAO.Recordset
  8. Dim strsql As String
  9. Dim counter As Integer
  10.  
  11. Set db = CurrentDb
  12.  
  13. strsql = "SELECT Count(tblhselog.HSEID) AS CountOfHSEID "
  14. strsql = strsql & "FROM tblhselog "
  15. strsql = strsql & "WHERE (((Format([Incident_date],""mmm""))='" & cmboMonth & "') AND ((Format([Incident_date],""yyyy""))=" & cmboYear & ") AND ((tblhselog.Department)=" & cmboDept & ") AND ((tblhselog.Incident_type)=" & IncidentTyping & "));"
  16. 'Debug.Print strsql
  17. Set rs = db.OpenRecordset(strsql, dbOpenSnapshot)
  18. If rs.RecordCount <> 0 Then
  19. counter = rs("CountOfHSEID")
  20. Else
  21. counter = 0
  22. End If
  23.  
  24. 'Debug.Print counter
  25. CountingIncidents = counter
  26.  
  27.  
  28. completedyo:
  29. rs.Close
  30. db.Close
  31. Set rs = Nothing
  32. Set db = Nothing
  33. Exit Function
  34.  
  35. jumpout:
  36. MsgBox Err.Description & " - " & Err.Number
  37. Resume completedyo
  38.  
  39. End Function

Second function to count fiscal year to date figures:
Expand|Select|Wrap|Line Numbers
  1. 'Function to return the total number of a type of incident in current fiscal year selected
  2. Public Function FiscalCountingIncidents(countMonth As String, countYear As Integer, countDept As Integer, countIncident As Integer)
  3.  
  4. On Error GoTo jumpout
  5.  
  6. Dim dbz As DAO.Database
  7. Dim rsz As DAO.Recordset
  8. Dim strsqlz As String
  9. Dim counterz As Integer
  10.  
  11. Set dbz = CurrentDb
  12.  
  13. strsqlz = "SELECT Count(tblhselog.HSEID) AS CountOfHSEID"
  14. strsqlz = strsqlz & " FROM tblhselog"
  15. strsqlz = strsqlz & " WHERE (((tblhselog.Incident_date) Between fiscalStartDate('" & countMonth & "'," & countYear & ") And fiscalEndDate('" & countMonth & "'," & countYear & ")) AND ((tblhselog.Department)=" & countDept & "))"
  16. strsqlz = strsqlz & " GROUP BY tblhselog.Incident_type"
  17. strsqlz = strsqlz & " HAVING (((tblhselog.Incident_type)=" & countIncident & "));"
  18.  
  19.  
  20. 'Debug.Print strsqlz
  21. Set rsz = dbz.OpenRecordset(strsqlz, dbOpenSnapshot)
  22. If rsz.RecordCount <> 0 Then
  23. counterz = rsz("CountOfHSEID")
  24. Else
  25. counterz = 0
  26. End If
  27.  
  28. 'Debug.Print counterz
  29. FiscalCountingIncidents = counterz
  30.  
  31.  
  32. completedyo:
  33. rsz.Close
  34. dbz.Close
  35. Set rsz = Nothing
  36. Set dbz = Nothing
  37. Exit Function
  38.  
  39. jumpout:
  40. MsgBox Err.Description & " - " & Err.Number
  41. Resume completedyo
  42.  
  43. 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:

Expand|Select|Wrap|Line Numbers
  1. 'The following function is used to calculate the fiscal year end date given the month and year
  2.  
  3. Function fiscalEndDate(monthvalue As String, yearvalue As Integer) As String
  4.  
  5. Select Case monthvalue
  6. Case "Jan"
  7. monthvalue = 1
  8. Case "Feb"
  9. monthvalue = 2
  10. Case "Mar"
  11. monthvalue = 3
  12. Case "Apr"
  13. monthvalue = 4
  14. Case "May"
  15. monthvalue = 5
  16. Case "Jun"
  17. monthvalue = 6
  18. Case "Jul"
  19. monthvalue = 7
  20. Case "Aug"
  21. monthvalue = 8
  22. Case "Sep"
  23. monthvalue = 9
  24. Case "Oct"
  25. monthvalue = 10
  26. Case "Nov"
  27. monthvalue = 11
  28. Case "Dec"
  29. monthvalue = 12
  30. Case Else
  31. MsgBox "month value must be supplied"
  32. Exit Function
  33. End Select
  34.  
  35. If (monthvalue >= 10) Then
  36. fiscalEndDate = "30/09/" & yearvalue + 1
  37. Else
  38. fiscalEndDate = "30/09/" & yearvalue
  39. End If
  40. End Function
  41.  
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.
Jan 7 '11 #1
5 1972
munkee
374 256MB
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?
Jan 7 '11 #2
ADezii
8,800 Expert 8TB
The method in which you utilize the Function is very unorthodox in that you are modifying one of the Arguments passed to it. Try:
Expand|Select|Wrap|Line Numbers
  1. Function fiscalEndDate(monthvalue As String, yearvalue As Integer) As String
  2. Dim bytMonthValue As Byte
  3.  
  4. Select Case monthvalue
  5.   Case "Jan"
  6.     bytMonthValue = 1
  7.   Case "Feb"
  8.     bytMonthValue = 2
  9.   Case "Mar"
  10.     bytMonthValue = 3
  11.   Case "Apr"
  12.     bytMonthValue = 4
  13.   Case "May"
  14.     bytMonthValue = 5
  15.   Case "Jun"
  16.     bytMonthValue = 6
  17.   Case "Jul"
  18.     bytMonthValue = 7
  19.   Case "Aug"
  20.     bytMonthValue = 8
  21.   Case "Sep"
  22.     bytMonthValue = 9
  23.   Case "Oct"
  24.     bytMonthValue = 10
  25.   Case "Nov"
  26.     bytMonthValue = 11
  27.   Case "Dec"
  28.     bytMonthValue = 12
  29.   Case Else
  30.     MsgBox "month value must be supplied"
  31.       Exit Function
  32. End Select
  33.  
  34. If (bytMonthValue >= 10) Then
  35.   fiscalEndDate = "30/09/" & CStr(yearvalue + 1)
  36. Else
  37.   fiscalEndDate = "30/09/" & CStr(yearvalue)
  38. End If
  39. End Function
Jan 7 '11 #3
munkee
374 256MB
Thanks for the modification Adezii. Im really weak on function coding to be honest even though they are such a simple concept.
Jan 7 '11 #4
gershwyn
122 100+
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.
Jan 7 '11 #5
munkee
374 256MB
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.
Jan 8 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Chua Wen Ching | last post: by
reply views Thread by paragdere | last post: by
reply views Thread by paragdere | last post: by
4 posts views Thread by vir | last post: by
3 posts views Thread by jer006 | last post: by
8 posts views Thread by PriyeshSax | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.