Multiple SQL Stuff

[DS]

I have this function that I'm using to round time with and I'm trying to
Update a record with SQL but the whole thing does nothing....any help
appreciated.
Thanks
DS

Private Sub CommandOne_Click()
Function RoundTime(TimeIn As Date) As Date

Select Case Minute(Forms!frmTimeCardAdjust!TxtTimeIN)
Case 0 To 15
RoundTime = TimeValue(Hour(Forms!frmTimeCardAdjust!TxtTimeIN) & ":15")
Case 16 To 30
RoundTime = TimeValue(Hour(Forms!frmTimeCardAdjust!TxtTimeIN) & ":30")
Case 31 To 45
RoundTime = TimeValue(Hour(Forms!frmTimeCardAdjust!TxtTimeIN) & ":45")
Case Else
RoundTime = TimeValue(Hour(Forms!frmTimeCardAdjust!TxtTimeIN) + 1 & ":00")
End Select
End Function

Dim ONESQL As String
DoCmd.SetWarnings False
ONESQL = "UPDATE tblTimeLog SET tblTimeLog.LogTimeIn = RoundTime " & _
"WHERE tblTimeLog.LogID = Forms!frmTimeCardAdjust!TxtLogID;"
DoCmd.RunSQL (ONESQL)
DoCmd.SetWarnings True
DoCmd.Close acForm, "frmTieAdjustSelect"
End Sub

 

[Douglas J. Steele]

Try using the TimeSerial function:

RoundTime = TimeSerial(Hour(Forms!frmTimeCardAdjust!TxtTimeIN), 15, 0)

Of course, you've got a syntax error in what you've written: you've got the
function inside the sub, which isn't allowed.

 

[Tom Wickerath]

Hi DS,

A couple of comments...

First, you'll see that something does happen if you click on Debug > Compile
ProjectName. You will get a compile error: "Expected End Sub". The reason is
that you cannot start a function within the click event procedure as you have
done. You can declare a date/time variable and initialize it by setting it
equal to the return value of the RoundTime function. Something like this,
however, I still do not like the return value of the RoundTime function,
because it assumes that the date is 0 (Dec. 30, 1899). So, you should work on
that a bit more.


Option Compare Database
Option Explicit

Private Sub CommandOne_Click()
Dim dteTimeIn As Date
Dim dteRoundedTime As Date
Dim ONESQL As String

dteTimeIn = Forms!frmTimeCardAdjust!TxtTimeIN
dteRoundedTime = RoundTime(dteTimeIn)

ONESQL = "UPDATE tblTimeLog SET tblTimeLog.LogTimeIn = #" _
& dteRoundedTime & "# WHERE tblTimeLog.LogID = '" _
& Forms!frmTimeCardAdjust!TxtLogID & "';"

'Debug.Print ONESQL

CurrentDb.Execute ONESQL, dbFailOnError

DoCmd.Close acForm, "frmTieAdjustSelect"

End Sub

Function RoundTime(TimeIn As Date) As Date

Select Case Minute(Forms!frmTimeCardAdjust!TxtTimeIN)
Case 0 To 15
RoundTime = TimeValue(Hour(Forms!frmTimeCardAdjust!TxtTimeIN) & ":15")
Case 16 To 30
RoundTime = TimeValue(Hour(Forms!frmTimeCardAdjust!TxtTimeIN) & ":30")
Case 31 To 45
RoundTime = TimeValue(Hour(Forms!frmTimeCardAdjust!TxtTimeIN) & ":45")
Case Else
RoundTime = TimeValue(Hour(Forms!frmTimeCardAdjust!TxtTimeIN) + 1 & ":00")
End Select
End Function

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tom Wickerath]

PS.

I forgot to mention that if your tblTimeLog.LogID field is numeric, then you
need to remove the single quotes in the ONESQL assignment. The version I
showed the first time assumes that the LogID field is a text data type.

If LogID is a Text data type, use:
ONESQL = "UPDATE tblTimeLog SET tblTimeLog.LogTimeIn = #" _
& dteRoundedTime & "# WHERE tblTimeLog.LogID = '" _
& Forms!frmTimeCardAdjust!TxtLogID & "';"


If LogID is a number data type, use:

ONESQL = "UPDATE tblTimeLog SET tblTimeLog.LogTimeIn = #" _
& dteRoundedTime & "# WHERE tblTimeLog.LogID = " _
& Forms!frmTimeCardAdjust!TxtLogID


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

 

[DS]

Hi DS,

A couple of comments...

First, you'll see that something does happen if you click on Debug > Compile
ProjectName. You will get a compile error: "Expected End Sub". The reason is
that you cannot start a function within the click event procedure as you have
done. You can declare a date/time variable and initialize it by setting it
equal to the return value of the RoundTime function. Something like this,
however, I still do not like the return value of the RoundTime function,
because it assumes that the date is 0 (Dec. 30, 1899). So, you should work on
that a bit more.


Option Compare Database
Option Explicit

Private Sub CommandOne_Click()
Dim dteTimeIn As Date
Dim dteRoundedTime As Date
Dim ONESQL As String

dteTimeIn = Forms!frmTimeCardAdjust!TxtTimeIN
dteRoundedTime = RoundTime(dteTimeIn)

ONESQL = "UPDATE tblTimeLog SET tblTimeLog.LogTimeIn = #" _
& dteRoundedTime & "# WHERE tblTimeLog.LogID = '" _
& Forms!frmTimeCardAdjust!TxtLogID & "';"

'Debug.Print ONESQL

CurrentDb.Execute ONESQL, dbFailOnError

DoCmd.Close acForm, "frmTieAdjustSelect"

End Sub

Function RoundTime(TimeIn As Date) As Date

Select Case Minute(Forms!frmTimeCardAdjust!TxtTimeIN)
Case 0 To 15
RoundTime = TimeValue(Hour(Forms!frmTimeCardAdjust!TxtTimeIN) & ":15")
Case 16 To 30
RoundTime = TimeValue(Hour(Forms!frmTimeCardAdjust!TxtTimeIN) & ":30")
Case 31 To 45
RoundTime = TimeValue(Hour(Forms!frmTimeCardAdjust!TxtTimeIN) & ":45")
Case Else
RoundTime = TimeValue(Hour(Forms!frmTimeCardAdjust!TxtTimeIN) + 1 & ":00")
End Select
End Function

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Great Thank You, I works. I see the Error of my ways!
DS

 

[DS]

Try using the TimeSerial function:

RoundTime = TimeSerial(Hour(Forms!frmTimeCardAdjust!TxtTimeIN), 15, 0)

Of course, you've got a syntax error in what you've written: you've got the
function inside the sub, which isn't allowed.

Thanks Douglas, I'll play around with that!
DS

 

[Tom Wickerath]

Hi DS,

I wonder if you really want to round the LogTimeIn values as you are (ie.
always rounding up). This sounds more appropriate for a LogTimeOut value. The
reason I say this is that if LogTimeIn represents the clock in time for
employees, then they could be getting short-changed on their work hours. For
example, if the value entered into txtTimeIn is 12-Nov-2006 1:46 PM, the
value stored in the LogTimeIn field will be 12/30/1899 2:00 PM (you can see
the date by setting a format of mm/dd/yyyy hh:nn ampm on this field). Thus,
you are indicating that they started work 14 minutes later than actual (not
counting for the date issue). Over time, such rounding operations could lead
to a significant deduction from the actual hours worked, which could get your
company in deep do-do with some government agency that regulates labor in
your country.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________