Calculating Time At Severities, almost working! :)

[robjones23]

Hello All,

I've got a database which has several fields but the ones I'm interested in are:

Seconds1 Sev Seconds2 Sev2 Seconds3 Sev3 etc...


Basically Seconds 1 holds a number in seconds of outage time, Sev tells me which severity the incident was at at the time. So for example the database would look like this:

14756, 3, 749, 5, 7468, 1, 9974, 1

What I want is for Some code to look at each row in the table and where it sees a "1" "2" "3" "4" "5" in Sev it adds up the figures and then returns this:

"Time at Sev1 XX:XX, time at Sev2 YY:YY" etc....

At the mo I'm trying to get it working for Sev1 only using the following code:

Public Function Sevcalc(secondsa As Integer, Seva As Integer, secondsb As Integer, Sevb As Integer, secondsc As Integer, Sevc As Integer, secondsd As Integer, Sevd As Integer) As String
    
    Dim secs1a As Integer
    Dim secs1b As Integer
    Dim secs1c As Integer
    Dim secs1d As Integer
    Dim Secs1Total As String
    Dim secs2 As Integer
    Dim secs3 As Integer
    Dim secs4 As Integer
         
 If Seva = 1 Then
    secs1a = secondsa
 End If
 
 If Sevb = 1 Then
    secs1b = secondsb
 End If

 If Sevc = 1 Then
    secs1c = secondsc
 End If

 If Sevd = 1 Then
    secs1d = secondsd
 End If

Secs1Total = HoursAndMinutes((secs1a + secs1b + secs1c + secs1d) / 1440 / 60)
 
Sevcalc = "Time at Sev1" & " " & Secs1Total
End Function

This works fine for the first row but all the other rows return #Error. I'm assuming this is because I need to "restart" the function for each row?? I don't know how to do that!! Can anyone help??

This might be a dirty way to do it so any other ideas would be welcomed

Note that I'm trying to get the sev1 bit working first and then replicate that for other severities which is why there are unused bits there

Cheers,

Rob.

 

[Epic]

Try to open a recordset and loop between the records or create a query and use the Sevcalc function... if is saved in a module then your function is available in the Expression builder... it will slow down your query but will work...

 

[Mile-O]

Seconds1 Sev Seconds2 Sev2 Seconds3 Sev3 etc...

Change to a normalised data structure and you'll find this extremely easy to do without the need of a custom function. All you'd need is one DSum() function.

 

[robjones23]

Ok,

so I've come back to this and don't really understand what MJ is talking about, and I've still not done the open recordset option as yet. However, the code below is working for some, but not for others and I still don't understand why.

The attached Xls file shows an example from the database. As you can see, some of them work, others return the error message however they all SEEM the same. All are the same format etc......

Public Function Sevcalc(secondsa As Integer, Seva As Integer, secondsb As Integer, Sevb As Integer, secondsc As Integer, Sevc As Integer, secondsd As Integer, Sevd As Integer) As String
    
    'Sev1 Declarations
    Dim secs1a As Integer
    Dim secs1b As Integer
    Dim secs1c As Integer
    Dim secs1d As Integer
    Dim Secs1Total As String
    'Sev 2 Declarations
    Dim secs2a As Integer
    Dim secs2b As Integer
    Dim secs2c As Integer
    Dim secs2d As Integer
    Dim Secs2Total As String
    'Sev 3 Declarations
    Dim secs3a As Integer
    Dim secs3b As Integer
    Dim secs3c As Integer
    Dim secs3d As Integer
    Dim Secs3Total As String
    'Sev 4 Declarations
    Dim secs4a As Integer
    Dim secs4b As Integer
    Dim secs4c As Integer
    Dim secs4d As Integer
    Dim Secs4Total As String
    'Sev 5 Declarations
    Dim secs5a As Integer
    Dim secs5b As Integer
    Dim secs5c As Integer
    Dim secs5d As Integer
    Dim Secs5Total As String
    'Sev 0 Declarations
    Dim secs0a As Integer
    Dim secs0b As Integer
    Dim secs0c As Integer
    Dim secs0d As Integer
    Dim Secs0Total As String
         
' Sev 1 Calculation
         
 If Seva = 1 Then
    secs1a = secondsa
 End If
 
 If Sevb = 1 Then
    secs1b = secondsb
 End If

 If Sevc = 1 Then
    secs1c = secondsc
 End If

 If Sevd = 1 Then
    secs1d = secondsd
 End If

Secs1Total = HoursAndMinutes((secs1a + secs1b + secs1c + secs1d) / 1440 / 60)
 
' Sev 2 Calculation
 
 If Seva = 2 Then
    secs2a = secondsa
 End If
 
 If Sevb = 2 Then
    secs2b = secondsb
 End If

 If Sevc = 2 Then
    secs2c = secondsc
 End If

 If Sevd = 2 Then
    secs2d = secondsd
 End If

Secs2Total = HoursAndMinutes((secs2a + secs2b + secs2c + secs2d) / 1440 / 60)
 
' Sev 3 Calculation
 
 If Seva = 3 Then
    secs3a = secondsa
 End If
 
 If Sevb = 3 Then
    secs3b = secondsb
 End If

 If Sevc = 3 Then
    secs3c = secondsc
 End If

 If Sevd = 3 Then
    secs3d = secondsd
 End If

Secs3Total = HoursAndMinutes((secs3a + secs3b + secs3c + secs3d) / 1440 / 60)
  
' Sev 4 Calculation
 
 If Seva = 4 Then
    secs4a = secondsa
 End If
 
 If Sevb = 4 Then
    secs4b = secondsb
 End If

 If Sevc = 4 Then
    secs4c = secondsc
 End If

 If Sevd = 4 Then
    secs4d = secondsd
 End If

Secs4Total = HoursAndMinutes((secs4a + secs4b + secs4c + secs4d) / 1440 / 60)
   
' Sev 5 Calculation
 
 If Seva = 5 Then
    secs5a = secondsa
 End If
 
 If Sevb = 5 Then
    secs5b = secondsb
 End If

 If Sevc = 5 Then
    secs5c = secondsc
 End If

 If Sevd = 5 Then
    secs5d = secondsd
 End If

Secs5Total = HoursAndMinutes((secs5a + secs5b + secs5c + secs5d) / 1440 / 60)
   
' Sev 0 Calculation
 
 If Seva = 0 Then
    secs0a = secondsa
 End If
 
 If Sevb = 0 Then
    secs0b = secondsb
 End If

 If Sevc = 0 Then
    secs0c = secondsc
 End If

 If Sevd = 0 Then
    secs0d = secondsd
 End If

Secs0Total = HoursAndMinutes((secs0a + secs0b + secs0c + secs0d) / 1440 / 60)
   
   
      
Sevcalc = "Time at Sev1" & " " & Secs1Total & " " & "Time at Sev2" & " " & Secs2Total & " " & "Time at Sev3" & " " & Secs3Total & " " & "Time at Sev4" & " " & Secs4Total & " " & "Time at Sev5" & " " & Secs5Total & " " & "Time at Sev0" & " " & Secs0Total
End Function

 

[Bat17]

I am a little cofused as how your function relates to the data, you are passing the info as Seconds but it is stored as time.
is 29:33
29.33 seconds
29 Mins 33 Seconds
If the data is stored in time why are you conerting back and forth with integers?
Is this a table in you db or are you reading it from a text file?

Peter

 

[robjones23]

The times are input into a form and then the forms text fields are appended to the database table. I use the seconds figure to calculate numerical outage times (we need hours:minutes as well as a numeric time, e.g. 1 hour 30 minutes is 01:30 as time and 1.5 as numeric value) So thats why I've got seconds in there as well.

The time (hh:mm) is figured out using the hoursandminutes function from the MSDN site which i believe comes from northwind.

Hope that answers your questions!

 

[Bat17]

I dont think that there is anything wrong with the function itself. I think that you need to try running it on a set of duff data and stepping through to see where the error is creeping in.
How are you calling the function, from a query or another function?

Peter

 

[robjones23]

It's running from a query - I've never stepped through a module before - where's the option? <-------- Newbie spotted!!!

 

[Bat17]

First of limit the data that you a going to look at or you will be there all day

go to your code and right click on the line "If Seva = 1 Then" and select Toggle>Breakpoint from the shortcut menu, this should highlight the line of code in red.
now run your query, the code pane will open and it will be higlighting that row of code. Pressing F8 will let you go through your code one line at a time. If you hover the mouse over your variables they will pop up a bubble with there value so that you can see if you are getting what you expected.

HTH

Peter

 

[robjones23]

Hmmmm.... strange.

on the instances where it works, it steps through each step fine, however when the #error is produced, it simply doesn't step into it, it's like it ignores the breakpoint (which i placed at the Public function at the top of the modules)....

Any ideas??

 

[robjones23]

here's the actual database with two lines of data in it, one which works, and one which produces the error message.

Could you take a look???

 

[Bat17]

Just had a quick look and the error is comming from the data-type.
In the function you are passing in values as Integer which will give a max value of 32,767 and you are trying to pass it a value of 106,370 so if you really get numbers this big and it is not a typo then you need to change all of those data types from Integer to Long.

I will try to look at the db further if I get a chance

HTH

Peter

 

[robjones23]

That's done it, it works now!

I just thought integer meant number, didn't realise it had a maximum value attached to it. The whole lot calculates now which is great! Thanks ver much for all the help Peter it's greatly appreciated!

Rob.

 

[Bat17]

Sneaky things datatypes, bite you when you are not looking!

Peter