Another Code Wizard Question

  • Thread starter Thread starter Henro
  • Start date Start date
H

Henro

Below I have the code to calculate hours above 23:59. THe thing is, it seems
to be 'hardcoded' to the field [InstBSItijd] in the table MIS. The table is
OK but how do I make a variable of the field InstBSItijd so that I don't
have to write this code 20 times for the twenty fields on which I need to
use this?

TIA Henro

Function VerzamelUren()

Dim db As DAO.Database, rs As DAO.Recordset
Dim totaaluren As Long, totaalminuten As Long
Dim dagen As Long, uren As Long, minuten As Long
Dim interval As Variant, j As Integer
Dim Kolom




Set db = DBEngine.workspaces(0).databases(0)
Set rs = db.OpenRecordset("Urenregistratie")
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs![InstBSItijd]
rs.MoveNext
Wend
totaaluren = Int(CSng(interval * 24))
totaalminuten = Int(CSng(interval * 1440))
uren = totaaluren Mod 24
minuten = totaalminuten Mod 60

VerzamelUren = totaaluren & " uren en " & minuten & " minuten"

End Function
 
Hi Henro,

Glad to see that you reset your system clock! Try this variation of your code. Note: You
can run this from the Debug window (Ctrl + G) by entering: ?
VerzamelUren("InstBSItijd")

Tom

'------Begin Code-----------

Option Compare Database
Option Explicit

Function VerzamelUren(strFieldName As String) As String

Dim db As DAO.Database, rs As DAO.Recordset
Dim totaaluren As Long, totaalminuten As Long
Dim uren As Long, minuten As Long
Dim interval As Variant, j As Integer

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("Urenregistratie")

interval = #12:00:00 AM#

While Not rs.EOF
interval = interval + rs(strFieldName)
rs.MoveNext
Wend

totaaluren = Int(CSng(interval * 24))
totaalminuten = Int(CSng(interval * 1440))
uren = totaaluren Mod 24
minuten = totaalminuten Mod 60

VerzamelUren = totaaluren & " uren en " & minuten & " minuten"

rs.Close
db.Close

End Function

'------End Code-----------

________________________________________


Below I have the code to calculate hours above 23:59. THe thing is, it seems
to be 'hardcoded' to the field [InstBSItijd] in the table MIS. The table is
OK but how do I make a variable of the field InstBSItijd so that I don't
have to write this code 20 times for the twenty fields on which I need to
use this?

TIA Henro

Function VerzamelUren()

Dim db As DAO.Database, rs As DAO.Recordset
Dim totaaluren As Long, totaalminuten As Long
Dim dagen As Long, uren As Long, minuten As Long
Dim interval As Variant, j As Integer
Dim Kolom


Set db = DBEngine.workspaces(0).databases(0)
Set rs = db.OpenRecordset("Urenregistratie")
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs![InstBSItijd]
rs.MoveNext
Wend
totaaluren = Int(CSng(interval * 24))
totaalminuten = Int(CSng(interval * 1440))
uren = totaaluren Mod 24
minuten = totaalminuten Mod 60

VerzamelUren = totaaluren & " uren en " & minuten & " minuten"

End Function
 
You need to pass in a variant instead of a string. You can test a variant for a null
condition and proceed to run the code accordingly. A variant is the only variable type
that can be tested for null.

Try the code shown below. Note: I added error handling this time.

From debug window:
?VerzamelUren("InstBSItijd")


'------Begin Code-----------

Option Compare Database
Option Explicit

Function VerzamelUren(varFieldName As Variant) As String
On Error GoTo ProcError

Dim db As DAO.Database, rs As DAO.Recordset
Dim totaaluren As Long, totaalminuten As Long
Dim uren As Long, minuten As Long
Dim interval As Variant, j As Integer

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("Urenregistratie")

interval = #12:00:00 AM#
rs.MoveFirst

While Not rs.EOF
If Not IsNull(rs(varFieldName)) Then
interval = interval + rs(varFieldName)
End If
rs.MoveNext
Wend

totaaluren = Int(CSng(interval * 24))
totaalminuten = Int(CSng(interval * 1440))
uren = totaaluren Mod 24
minuten = totaalminuten Mod 60

VerzamelUren = totaaluren & " uren en " & minuten & " minuten"

ExitProc:
On Error Resume Next
rs.Close
db.Close
Exit Function

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
, "Error in VerzamelUren event procedure..."
Resume ExitProc
End Function


'------End Code-----------


Tom

________________________________________


Got it running, but how do I handle NULL value's?
 
Dear Tom,

you are being a great help but i keep running into problems. A lot of the
fields I want to be totalized are fields that are calculated in a query. So,
in the table you will only find: StartDay and Endday, in the query you will
also find TotalDay: [Endday]-[Startday].
I want the calculated fields to be totalized to using your code below (which
does exactly what I want) but I can't seem to find a way to use this code in
a query? So I produced a bit of code that would put the values of the query
into a new table which I want to totalize. But here start's the problem: All
of the noncalculated fields are TEXT fields in the new table. Some(?) of the
calculated fields are Date/Time fields in the new table, others are Numeric.
If I change this manually in the table design of the new table ( change all
in numeric) everything gets converted nicely. I can use Access Wizards to
totalize everything and with the Datediff function I managed somehow to
translate it all in hours. But if I want to add new info to this new table I
get conversionerrors stating that the format of the receiving field is not
the same as the information from the query. If I replace the table using the
MAKE TABLE the field definitions are once a mess again. I would very much
like to use your code on the query "UrenregistratieQuery", then I wouldn't
have to bother with the new Tabel [MIS] at all. That query holds all
information I need.

If it is not possible to use your code on a a query, how would I do to get
everything totaled (and preferably averaged) ? If you like I can send you
the table's, query's and forms that are relevant in this story.

Grzt Henro
 
Dear Tom, your code is working but now I need to run this code per engineer.

I was thinking along the lines below. But it returns zero instead of the 16
hours plus I have. I think it's because 'Engineer' is not declared the right
way. Can you (or some other soul- and sanity saving codewizard point out
what my (obviously pretty small and stupid) mistake is? Thnx a LOT!

Henro.



NaamEngineer() is a function I wrote myself that returms the name of the
loggged in engineer (in my case it returns "Henro Veijer")

Option Compare Database
Option Explicit

Function TotME(varFieldName As Variant) As String
On Error GoTo ProcError

Dim db As DAO.Database, rs As DAO.Recordset
Dim totaaluren As Long, totaalminuten As Long
Dim uren As Long, minuten As Long
Dim interval As Variant, j As Integer
Dim Engineer As String

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("MIS")

interval = #12:00:00 AM#
rs.MoveFirst

While Not rs.EOF
If Not IsNull(rs(varFieldName)) And Engineer = NaamEngineer() Then
interval = interval + rs(varFieldName)
End If
rs.MoveNext
Wend

totaaluren = Int(CSng(interval * 24))
totaalminuten = Int(CSng(interval * 1440))
uren = totaaluren Mod 24
minuten = totaalminuten Mod 60

TotME = totaaluren
ExitProc:
On Error Resume Next
rs.Close
db.Close
Exit Function

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
, "Error in VerzamelUren event procedure..."
Resume ExitProc
End Function
 
Hi Henro,

It's pretty hard to diagnose such an issue without having the actual database to work on.
If you can zip it and send it to me, I will take a look at it for you.

You are currently evaluating NaamEngineer() for every pass through the loop. You really
only need to evaluate it one time, since it should remain constant. Also, you seem to be
testing the value of Engineer against the return value of this function, but it appears
that Engineer would be a zero length string at this point because it has not been assigned
a value yet.

Please take a step back and give us an overview of the "big picture". What exactly are
you trying to accomplish? If you are trying to get a total of hours worked by engineer,
would not a simple grouped report be much easier?

Tom
________________________________________

Dear Tom, your code is working but now I need to run this code per engineer.

I was thinking along the lines below. But it returns zero instead of the 16
hours plus I have. I think it's because 'Engineer' is not declared the right
way. Can you (or some other soul- and sanity saving codewizard point out
what my (obviously pretty small and stupid) mistake is? Thnx a LOT!

Henro.



NaamEngineer() is a function I wrote myself that returms the name of the
loggged in engineer (in my case it returns "Henro Veijer")

Option Compare Database
Option Explicit

Function TotME(varFieldName As Variant) As String
On Error GoTo ProcError

Dim db As DAO.Database, rs As DAO.Recordset
Dim totaaluren As Long, totaalminuten As Long
Dim uren As Long, minuten As Long
Dim interval As Variant, j As Integer
Dim Engineer As String

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("MIS")

interval = #12:00:00 AM#
rs.MoveFirst

While Not rs.EOF
If Not IsNull(rs(varFieldName)) And Engineer = NaamEngineer() Then
interval = interval + rs(varFieldName)
End If
rs.MoveNext
Wend

totaaluren = Int(CSng(interval * 24))
totaalminuten = Int(CSng(interval * 1440))
uren = totaaluren Mod 24
minuten = totaalminuten Mod 60

TotME = totaaluren
ExitProc:
On Error Resume Next
rs.Close
db.Close
Exit Function

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
, "Error in VerzamelUren event procedure..."
Resume ExitProc
End Function
 
I can possibly look at this tomorrow, time permitting. It's 4:00 in the morning for me
now, and time I went to bed!!

Tom
______________________________________


Dear Tom,

you are being a great help but i keep running into problems. A lot of the
fields I want to be totalized are fields that are calculated in a query. So,
in the table you will only find: StartDay and Endday, in the query you will
also find TotalDay: [Endday]-[Startday].
I want the calculated fields to be totalized to using your code below (which
does exactly what I want) but I can't seem to find a way to use this code in
a query? So I produced a bit of code that would put the values of the query
into a new table which I want to totalize. But here start's the problem: All
of the noncalculated fields are TEXT fields in the new table. Some(?) of the
calculated fields are Date/Time fields in the new table, others are Numeric.
If I change this manually in the table design of the new table ( change all
in numeric) everything gets converted nicely. I can use Access Wizards to
totalize everything and with the Datediff function I managed somehow to
translate it all in hours. But if I want to add new info to this new table I
get conversionerrors stating that the format of the receiving field is not
the same as the information from the query. If I replace the table using the
MAKE TABLE the field definitions are once a mess again. I would very much
like to use your code on the query "UrenregistratieQuery", then I wouldn't
have to bother with the new Tabel [MIS] at all. That query holds all
information I need.

If it is not possible to use your code on a a query, how would I do to get
everything totaled (and preferably averaged) ? If you like I can send you
the table's, query's and forms that are relevant in this story.

Grzt Henro
 
I'' mail the whole damned thing ;-p including explanation what I am trying
to do!

:-p

Thanks,

Henro
 
Back
Top