FAO John Vinson: re Auto-Calculations & Time

  • Thread starter Thread starter Duncan Edment
  • Start date Start date
D

Duncan Edment

John,

My sincerest apologies for not replying sooner. I've been tied up with
other
things, but here at last is my response. Please bear with me, as there a
number
of controls / calculations used. This may be the problem, and these could
be
reduced in number to speed things up. Perhaps you can let me know, and I
will
bow to your superior knowledge / experience!

As I mentioned in my first post, the form contains two calculated fields,
both
of which show a +/- figure depending on how many hours the employee has
worked,
both in week and since the start of the year.

1. The weekly +/- text box has a Control Source as follows:

=IIf(sfmFullTimeSheet.Form!txtSumDuration-(37/24)<0,"-","") &
DiffTime(sfmFullTimeSheet.Form!txtSumDuration,37/24,4)

2. The cumulative +/- text box has a Control Source as follows:

=IIf(sfmFullTimeSheet.Form!txtHoursDifference<0,"-","") &
DiffTime(sfmFullTimeSheet.Form!txtActualHours,
sfmFullTimeSheet.Form!txtRealNumHours,4)

3. The control sfmFullTimeSheet.Form!txtSumDuration, is an unbound control,
that
has its value set in the On Current event of the form, and the code is
as
follows:

Private Sub Form_Current()

Dim datCRecDate As Date
Dim datWkStart As Date
Dim datWkEnd As Date
Dim datSumDur As Date
Dim rstClone As DAO.Recordset

If IsNull(Me.fldDateWorked) = False Then
' code assumes that a week runs from Monday through Sunday
datCRecDate = Me.fldDateWorked
datWkStart = DateAdd("d", 1 - DatePart("w", datCRecDate, vbMonday),
_
datCRecDate)
datWkEnd = DateAdd("d", 6, datWkStart)
datSumDur = 0
Set rstClone = Me.RecordsetClone
rstClone.MoveFirst
Do While rstClone.EOF = False
If rstClone!fldDateWorked.Value >= datWkStart And _
rstClone!fldDateWorked.Value <= datWkEnd Then
If rstClone!fldIsWork.Value = True Then
If Not IsNull(rstClone!fldEndTime) And Not _
IsNull(rstClone!fldStartTime) Then
datSumDur = datSumDur + (rstClone!fldEndTime - _
rstClone!fldStartTime)
End If
End If
End If

rstClone.MoveNext
Loop

Me.txtSumDuration = datSumDur

Set rstClone = Nothing
End If

End Sub

4. The control sfmFullTimeSheet.Form!txtHoursDifference has a Control Source
of:

=[txtActualHours]-[txtRealNumHours]

5. The control sfmFullTimeSheet.Form!txtActualHours has a Control Source of:


=Sum(IIf([tblProjects.fldProjectID]=400000,0,IIf([tblProjects.fldProjectID]=
_
400006,0,IIf([tblProjects.fldProjectID]=400003,0.3083333333, _
[fldEndTime]-[fldStartTime])))) - [txtTotalOTHours]

6. The control sfmFullTimeSheet.Form!txtRealNumHours has a Control Source
of:

=((DateDiff("ww","23-12-2002",Now())+1)*37)/24

7. The controls fldStartTime and fldEndTime, hold HH:MM values, in 24 hour
format

8. The DiffTime function, I picked up from a post in the NG, with the
following
code:

'Author: © Copyright 2002 Pacific Database Pty Limited
' Graham R Seach (e-mail address removed)
' Phone: +61 2 9872 9594 Fax: +61 2 9872 9593
'
' You may freely use and distribute this code
' with any applications you may develop, on the
' condition that the copyright notice remains
' unchanged, and intact as part of the code. You
' may not sell or publish this code in any form
' without the express written permission of the
' copyright holder.
'
' Inputs: dteStart: The lower date.
' dteEnd: The upper date.
' iFormat: The enum value that specifies the
' output format. For example:
' dfHrFraction = fraction of an hour
' dfMinFraction = fraction of a minute
' dfH = Number of hours
' dfHM = Number of hours and minutes
' dfHMS = Number of hours, minutes and seconds
' vSeparator: User-defined separator used for dfH, dfHM,
' and dfHMS.

Public Enum DateFormat
dfHrFraction = 1
dfMinFraction = 2
dfH = 3
dfHM = 4
dfHMS = 5
End Enum

Public Function DiffTime(dteStart As Date, dteEnd As Date, iFormat As
DateFormat, Optional vSeparator As Variant = ":") As Variant
Dim iHr As Integer
Dim iMin As Integer
Dim iSec As Integer
Dim dteTemp As Date
Dim vTemp As Variant
Dim bSwapped As Boolean

DiffTime = Null
bSwapped = False

'Check that both dates are valid
If Not IsDate(dteStart) Or Not IsDate(dteEnd) Then
DoCmd.Beep
MsgBox "You must supply valid dates.", vbOKOnly + vbExclamation, _
"Invalid date"
Exit Function
End If

'Check that dteStart < dteEnd
If dteStart > dteEnd Then
'dteStart > dteEnd. Swap them
dteTemp = dteStart
dteStart = dteEnd
dteEnd = dteTemp
bSwapped = True
End If

'Calculate the time differences
iHr = Abs(DateDiff("h", dteStart, dteEnd)) - _
IIf(Format(dteStart, "nnss") <= Format(dteEnd, "nnss"), 0, 1)
dteStart = DateAdd("h", iHr, dteStart)

iMin = Abs(DateDiff("n", dteStart, dteEnd)) - _
IIf(Format(dteStart, "ss") <= Format(dteEnd, "ss"), 0, 1)
dteStart = DateAdd("n", iMin, dteStart)

iSec = Abs(DateDiff("s", dteStart, dteEnd))

'Setup the output format
Select Case iFormat
Case 1 'Return as a fraction of an hour
vTemp = iHr + (iMin / 60) + (iSec / 360)
Case 2 'Return as a fraction of a minute
vTemp = (iHr * 60) + iMin + (iSec / 60)
Case 3 'Return as Hour
vTemp = iHr
Case 4 'Return as Hour:Minute
vTemp = iHr & vSeparator & Format(iMin, "00")
Case 5 'Return as Hour:Minute:Second
vTemp = iHr & vSeparator & iMin & vSeparator & iSec
End Select

'Debug.Print iHr & ":" & iMin & ":" & iSec
'DiffTime = IIf(bSwapped, "-", "") & vTemp
DiffTime = vTemp
End Function


I hope you're still with me!!! I know it's a lot of code and controls,
which I
think is the problem. However, I'm not sure on how to cut it down and
streamline the calculation.

Please help and advise.

Regards

Duncan
 
It's a real pain, Duncan, to have to try to connect a post that starts a
brand-new thread to something that has gone before. I can tell you that John
Vinson answers many tens of posts in any given week and it'll be a real
chore for him to connect this to a previous message thread. If you are
following up on a previous discussion, post a reply to a message in the
thread where that discussion took place, even if you end up replying to
yourself.

For other good suggestions on effective use of newsgroups, see the FAQ at
http://www.mvps.org/access/netiquette.htm.

Larry Linson
Microsoft Access MVP


Duncan Edment said:
John,

My sincerest apologies for not replying sooner. I've been tied up with
other
things, but here at last is my response. Please bear with me, as there a
number
of controls / calculations used. This may be the problem, and these could
be
reduced in number to speed things up. Perhaps you can let me know, and I
will
bow to your superior knowledge / experience!

As I mentioned in my first post, the form contains two calculated fields,
both
of which show a +/- figure depending on how many hours the employee has
worked,
both in week and since the start of the year.

1. The weekly +/- text box has a Control Source as follows:

=IIf(sfmFullTimeSheet.Form!txtSumDuration-(37/24)<0,"-","") &
DiffTime(sfmFullTimeSheet.Form!txtSumDuration,37/24,4)

2. The cumulative +/- text box has a Control Source as follows:

=IIf(sfmFullTimeSheet.Form!txtHoursDifference<0,"-","") &
DiffTime(sfmFullTimeSheet.Form!txtActualHours,
sfmFullTimeSheet.Form!txtRealNumHours,4)

3. The control sfmFullTimeSheet.Form!txtSumDuration, is an unbound control,
that
has its value set in the On Current event of the form, and the code is
as
follows:

Private Sub Form_Current()

Dim datCRecDate As Date
Dim datWkStart As Date
Dim datWkEnd As Date
Dim datSumDur As Date
Dim rstClone As DAO.Recordset

If IsNull(Me.fldDateWorked) = False Then
' code assumes that a week runs from Monday through Sunday
datCRecDate = Me.fldDateWorked
datWkStart = DateAdd("d", 1 - DatePart("w", datCRecDate, vbMonday),
_
datCRecDate)
datWkEnd = DateAdd("d", 6, datWkStart)
datSumDur = 0
Set rstClone = Me.RecordsetClone
rstClone.MoveFirst
Do While rstClone.EOF = False
If rstClone!fldDateWorked.Value >= datWkStart And _
rstClone!fldDateWorked.Value <= datWkEnd Then
If rstClone!fldIsWork.Value = True Then
If Not IsNull(rstClone!fldEndTime) And Not _
IsNull(rstClone!fldStartTime) Then
datSumDur = datSumDur + (rstClone!fldEndTime - _
rstClone!fldStartTime)
End If
End If
End If

rstClone.MoveNext
Loop

Me.txtSumDuration = datSumDur

Set rstClone = Nothing
End If

End Sub

4. The control sfmFullTimeSheet.Form!txtHoursDifference has a Control Source
of:

=[txtActualHours]-[txtRealNumHours]

5. The control sfmFullTimeSheet.Form!txtActualHours has a Control Source of:=Sum(IIf([tblProjects.fldProjectID]=400000,0,IIf([tblProjects.fldProjectID]=
_
400006,0,IIf([tblProjects.fldProjectID]=400003,0.3083333333, _
[fldEndTime]-[fldStartTime])))) - [txtTotalOTHours]

6. The control sfmFullTimeSheet.Form!txtRealNumHours has a Control Source
of:

=((DateDiff("ww","23-12-2002",Now())+1)*37)/24

7. The controls fldStartTime and fldEndTime, hold HH:MM values, in 24 hour
format

8. The DiffTime function, I picked up from a post in the NG, with the
following
code:

'Author: © Copyright 2002 Pacific Database Pty Limited
' Graham R Seach (e-mail address removed)
' Phone: +61 2 9872 9594 Fax: +61 2 9872 9593
'
' You may freely use and distribute this code
' with any applications you may develop, on the
' condition that the copyright notice remains
' unchanged, and intact as part of the code. You
' may not sell or publish this code in any form
' without the express written permission of the
' copyright holder.
'
' Inputs: dteStart: The lower date.
' dteEnd: The upper date.
' iFormat: The enum value that specifies the
' output format. For example:
' dfHrFraction = fraction of an hour
' dfMinFraction = fraction of a minute
' dfH = Number of hours
' dfHM = Number of hours and minutes
' dfHMS = Number of hours, minutes and seconds
' vSeparator: User-defined separator used for dfH, dfHM,
' and dfHMS.

Public Enum DateFormat
dfHrFraction = 1
dfMinFraction = 2
dfH = 3
dfHM = 4
dfHMS = 5
End Enum

Public Function DiffTime(dteStart As Date, dteEnd As Date, iFormat As
DateFormat, Optional vSeparator As Variant = ":") As Variant
Dim iHr As Integer
Dim iMin As Integer
Dim iSec As Integer
Dim dteTemp As Date
Dim vTemp As Variant
Dim bSwapped As Boolean

DiffTime = Null
bSwapped = False

'Check that both dates are valid
If Not IsDate(dteStart) Or Not IsDate(dteEnd) Then
DoCmd.Beep
MsgBox "You must supply valid dates.", vbOKOnly + vbExclamation, _
"Invalid date"
Exit Function
End If

'Check that dteStart < dteEnd
If dteStart > dteEnd Then
'dteStart > dteEnd. Swap them
dteTemp = dteStart
dteStart = dteEnd
dteEnd = dteTemp
bSwapped = True
End If

'Calculate the time differences
iHr = Abs(DateDiff("h", dteStart, dteEnd)) - _
IIf(Format(dteStart, "nnss") <= Format(dteEnd, "nnss"), 0, 1)
dteStart = DateAdd("h", iHr, dteStart)

iMin = Abs(DateDiff("n", dteStart, dteEnd)) - _
IIf(Format(dteStart, "ss") <= Format(dteEnd, "ss"), 0, 1)
dteStart = DateAdd("n", iMin, dteStart)

iSec = Abs(DateDiff("s", dteStart, dteEnd))

'Setup the output format
Select Case iFormat
Case 1 'Return as a fraction of an hour
vTemp = iHr + (iMin / 60) + (iSec / 360)
Case 2 'Return as a fraction of a minute
vTemp = (iHr * 60) + iMin + (iSec / 60)
Case 3 'Return as Hour
vTemp = iHr
Case 4 'Return as Hour:Minute
vTemp = iHr & vSeparator & Format(iMin, "00")
Case 5 'Return as Hour:Minute:Second
vTemp = iHr & vSeparator & iMin & vSeparator & iSec
End Select

'Debug.Print iHr & ":" & iMin & ":" & iSec
'DiffTime = IIf(bSwapped, "-", "") & vTemp
DiffTime = vTemp
End Function


I hope you're still with me!!! I know it's a lot of code and controls,
which I
think is the problem. However, I'm not sure on how to cut it down and
streamline the calculation.

Please help and advise.

Regards

Duncan

--
"It's not enough to be busy . . . the question is:
what are we busy about?"
~ Henry David Thoreau

will
help.

Please do. Are you using DatePart("ww", <datefield>) to calculate the
number of weeks since the start of the year? That might be faster.
Click to expand...
Click to expand...
 
John,

My sincerest apologies for not replying sooner. I've been tied up with
other
things, but here at last is my response. Please bear with me, as there a
number
of controls / calculations used.
Click to expand...

Duncan, I'm sorry, but this is just too much to go through on an
unpaid-volunteer basis (especially as I'll be leaving for a family
visit tomorrow morning).

Do look into the builtin Access date/time functions such as DateDiff.
It appears (at a quick glance) that you're using the DiffTime function
to calculate (using multiple steps) a text string, and then picking
that text string apart to get time values. Using DateDiff directly may
be a lot more efficient.
 
Larry,

My reason for posting a new message in a new thread was, some time had
passed since John had replied to my original post--at bottom of current
post. As a result, I was unsure as to whether he would notice the post, let
alone remember it. So, in an attempt to "attract his attention", I created
a new thread, posting my response from his original reply.

If this was wrong, then I hold my hands up and await execution. However, I
was merely trying to catch John's eye and seek an answer to my question.

Rgds

Duncan

Larry Linson said:
It's a real pain, Duncan, to have to try to connect a post that starts a
brand-new thread to something that has gone before. I can tell you that John
Vinson answers many tens of posts in any given week and it'll be a real
chore for him to connect this to a previous message thread. If you are
following up on a previous discussion, post a reply to a message in the
thread where that discussion took place, even if you end up replying to
yourself.

For other good suggestions on effective use of newsgroups, see the FAQ at
http://www.mvps.org/access/netiquette.htm.

Larry Linson
Microsoft Access MVP


Duncan Edment said:
John,

My sincerest apologies for not replying sooner. I've been tied up with
other
things, but here at last is my response. Please bear with me, as there a
number
of controls / calculations used. This may be the problem, and these could
be
reduced in number to speed things up. Perhaps you can let me know, and I
will
bow to your superior knowledge / experience!

As I mentioned in my first post, the form contains two calculated fields,
both
of which show a +/- figure depending on how many hours the employee has
worked,
both in week and since the start of the year.

1. The weekly +/- text box has a Control Source as follows:

=IIf(sfmFullTimeSheet.Form!txtSumDuration-(37/24)<0,"-","") &
DiffTime(sfmFullTimeSheet.Form!txtSumDuration,37/24,4)

2. The cumulative +/- text box has a Control Source as follows:

=IIf(sfmFullTimeSheet.Form!txtHoursDifference<0,"-","") &
DiffTime(sfmFullTimeSheet.Form!txtActualHours,
sfmFullTimeSheet.Form!txtRealNumHours,4)

3. The control sfmFullTimeSheet.Form!txtSumDuration, is an unbound control,
that
has its value set in the On Current event of the form, and the code is
as
follows:

Private Sub Form_Current()

Dim datCRecDate As Date
Dim datWkStart As Date
Dim datWkEnd As Date
Dim datSumDur As Date
Dim rstClone As DAO.Recordset

If IsNull(Me.fldDateWorked) = False Then
' code assumes that a week runs from Monday through Sunday
datCRecDate = Me.fldDateWorked
datWkStart = DateAdd("d", 1 - DatePart("w", datCRecDate, vbMonday),
_
datCRecDate)
datWkEnd = DateAdd("d", 6, datWkStart)
datSumDur = 0
Set rstClone = Me.RecordsetClone
rstClone.MoveFirst
Do While rstClone.EOF = False
If rstClone!fldDateWorked.Value >= datWkStart And _
rstClone!fldDateWorked.Value <= datWkEnd Then
If rstClone!fldIsWork.Value = True Then
If Not IsNull(rstClone!fldEndTime) And Not _
IsNull(rstClone!fldStartTime) Then
datSumDur = datSumDur + (rstClone!fldEndTime - _
rstClone!fldStartTime)
End If
End If
End If

rstClone.MoveNext
Loop

Me.txtSumDuration = datSumDur

Set rstClone = Nothing
End If

End Sub

4. The control sfmFullTimeSheet.Form!txtHoursDifference has a Control Source
of:

=[txtActualHours]-[txtRealNumHours]

5. The control sfmFullTimeSheet.Form!txtActualHours has a Control Source of:
Click to expand...
=Sum(IIf([tblProjects.fldProjectID]=400000,0,IIf([tblProjects.fldProjectID]=
_
400006,0,IIf([tblProjects.fldProjectID]=400003,0.3083333333, _
[fldEndTime]-[fldStartTime])))) - [txtTotalOTHours]

6. The control sfmFullTimeSheet.Form!txtRealNumHours has a Control Source
of:

=((DateDiff("ww","23-12-2002",Now())+1)*37)/24

7. The controls fldStartTime and fldEndTime, hold HH:MM values, in 24 hour
format

8. The DiffTime function, I picked up from a post in the NG, with the
following
code:

'Author: © Copyright 2002 Pacific Database Pty Limited
' Graham R Seach (e-mail address removed)
' Phone: +61 2 9872 9594 Fax: +61 2 9872 9593
'
' You may freely use and distribute this code
' with any applications you may develop, on the
' condition that the copyright notice remains
' unchanged, and intact as part of the code. You
' may not sell or publish this code in any form
' without the express written permission of the
' copyright holder.
'
' Inputs: dteStart: The lower date.
' dteEnd: The upper date.
' iFormat: The enum value that specifies the
' output format. For example:
' dfHrFraction = fraction of an hour
' dfMinFraction = fraction of a minute
' dfH = Number of hours
' dfHM = Number of hours and minutes
' dfHMS = Number of hours, minutes and seconds
' vSeparator: User-defined separator used for dfH, dfHM,
' and dfHMS.

Public Enum DateFormat
dfHrFraction = 1
dfMinFraction = 2
dfH = 3
dfHM = 4
dfHMS = 5
End Enum

Public Function DiffTime(dteStart As Date, dteEnd As Date, iFormat As
DateFormat, Optional vSeparator As Variant = ":") As Variant
Dim iHr As Integer
Dim iMin As Integer
Dim iSec As Integer
Dim dteTemp As Date
Dim vTemp As Variant
Dim bSwapped As Boolean

DiffTime = Null
bSwapped = False

'Check that both dates are valid
If Not IsDate(dteStart) Or Not IsDate(dteEnd) Then
DoCmd.Beep
MsgBox "You must supply valid dates.", vbOKOnly + vbExclamation, _
"Invalid date"
Exit Function
End If

'Check that dteStart < dteEnd
If dteStart > dteEnd Then
'dteStart > dteEnd. Swap them
dteTemp = dteStart
dteStart = dteEnd
dteEnd = dteTemp
bSwapped = True
End If

'Calculate the time differences
iHr = Abs(DateDiff("h", dteStart, dteEnd)) - _
IIf(Format(dteStart, "nnss") <= Format(dteEnd, "nnss"), 0, 1)
dteStart = DateAdd("h", iHr, dteStart)

iMin = Abs(DateDiff("n", dteStart, dteEnd)) - _
IIf(Format(dteStart, "ss") <= Format(dteEnd, "ss"), 0, 1)
dteStart = DateAdd("n", iMin, dteStart)

iSec = Abs(DateDiff("s", dteStart, dteEnd))

'Setup the output format
Select Case iFormat
Case 1 'Return as a fraction of an hour
vTemp = iHr + (iMin / 60) + (iSec / 360)
Case 2 'Return as a fraction of a minute
vTemp = (iHr * 60) + iMin + (iSec / 60)
Case 3 'Return as Hour
vTemp = iHr
Case 4 'Return as Hour:Minute
vTemp = iHr & vSeparator & Format(iMin, "00")
Case 5 'Return as Hour:Minute:Second
vTemp = iHr & vSeparator & iMin & vSeparator & iSec
End Select

'Debug.Print iHr & ":" & iMin & ":" & iSec
'DiffTime = IIf(bSwapped, "-", "") & vTemp
DiffTime = vTemp
End Function


I hope you're still with me!!! I know it's a lot of code and controls,
which I
think is the problem. However, I'm not sure on how to cut it down and
streamline the calculation.

Please help and advise.

Regards

Duncan

--
"It's not enough to be busy . . . the question is:
what are we busy about?"
~ Henry David Thoreau

John Vinson said:
On Mon, 24 Nov 2003 23:56:15 -0000, "Duncan Edment"

If required, I can post contents of the calculation fields, if this
Click to expand...
will
help.

Please do. Are you using DatePart("ww", <datefield>) to calculate the
number of weeks since the start of the year? That might be faster.
Click to expand...
Click to expand...
Click to expand...
 
John,

I appreciate the fact that you have taken the time to respond to my post,
albeit not with the solution that I was hoping for! :)

I appreciate the help that you have provided on the NG, much of which I have
used myself in various files, and I appreciate that your position is on an
unpaid-volunteer basis. I guess I am just looking for too much.

Regards

Duncan
 
Duncan,

Can you precisely and concisely state the problem you are trying to address,
what data you have, and what results you want, rather than posting lots of
code for someone to help with debugging?

Larry Linson
Microsoft Access MVP
 
The question originally posted to John, was one of speed. A form has two
calculated fields within it, both of which were taking an excruciatingly
slow time to calculate and update. I had asked in the NG if anyone could
help, and John kindly replied, asking me to post the contents of the fields.

This I duly did, and after posting the working code, John advised that it
would take too long for him to look at it. This I understand and bear no
grudge against John for this.

My aim was not for John to debug the code, as it work already. I merely had
asked for some input into how I could speed the calculation up.

Rgds

Duncan
 
The question originally posted to John,
was one of speed. A form has two
calculated fields within it, both of which
were taking an excruciatingly slow time
to calculate and update. . . .
My aim was not for John to debug
the code, as it work already. I merely
had asked for some input into how I
could speed the calculation up.
Click to expand...

Performance improvement is in the general category of debugging -- I wasn't
implying that your code doesn't work, only that it still needs some work (as
you've pointed out).

But, we can't tell you how to improve the performance of the code without
understanding it. Well, I suppose we could -- delete all that code and it'll
run faster; won't run properly but faster. There's a quote in the book
"Hardcore Visual Basic" that applies "If the code doesn't work, it doesn't
matter how fast it is."

And, it is probably possible, given enough time and dedication, for someone
to read and interpret the code and figure out what it is doing, so as to
make performance improvements without doing damage to it working. But, as
John said, that's more than a bit much.

My point was, and is, if you can precisely and concisely define the problem
for us, it is remotely possible that we will be better able to help. Perhaps
someone could even suggest an alternative approach, without having to
analyze the code line by line.

I see a loop through the records in the Recordsetclone. That could be
time-consuming -- it may also be necessary; but if there's some other way to
accomplish what you need, then that other way might be faster. But, like
John, I just don't have the time nor energy to sit down and do a detailed
analysis of the code to figure out what it is that you are trying to
accomplish.

Larry Linson
Microsoft Access MVP
 
Back
Top