troubleshooting Question

Rounding in SQL Update statement in Excel macro to update data in Access db

Avatar of W D
W DFlag for United States of America asked on
Microsoft AccessMicrosoft ExcelSQL
19 Comments1 Solution564 ViewsLast Modified:
I have an Excel macro that imports data into an Access db. Part of this macro includes this SQL Update statement created by an Experts Exchange expert (since I don't know how to do SQL update statements in macros, the expert provided some code):
  'After import, update the math for Rounded Time if the Invoice Method changes in the spreadsheet
    sSQL = "UPDATE Jobs, Timesheets Set Timesheets.RoundedTime = Int(-Timesheets.ActualTime * 4 + 0.5) / -4 "
    sSQL = sSQL & "WHERE Jobs.JobID = Timesheets.JobID AND Jobs.InvoiceMethod = 'Hourly' AND Timesheets.IsOperational AND Timesheets.OperationCode <> 100"

This SQL statement updates records in the db. Unfortunately, certain records where employees have  .08 in their Actual Time values (it could be .08, 1.08, 2.08, etc.), the .08 should be updated to .25 but it is updated to be 0. This has caused hundreds of records to be changed and I have to go in and update them manually.

What is the statement: Int(-Timesheets.ActualTime * 4 + 0.5) / -4  doing?
What's the reason to have negative signs in two places? How come it has Int in front of the statement?
I can't figure out what it's doing to .08 to make it 0. How do I deskcheck this with test values when I don't even know what it means?

I have code (from another Experts Exchange expert, since I didn't know VBA) in my Access form that works perfectly; it updates anything with .08 to .25 where appropriate.

Here's the code from the Access form:
Private Sub ActualTime_AfterUpdate()
Dim remainder As Double
Dim mynum As Double
Dim myint As Integer
Dim rounded As Double
Dim result As Double
Dim strInvoiceMethod As String
Dim blnIsOperational As Boolean
Dim strOpCode As String

'If there isn't an Operation Code, then inform user
If IsNull(Me.OperationCode) Then
        MsgBox "Enter an Operation Code"
End If

mynum = Me.Controls("ActualTime").Value 'this is the num of hours, taken from ActualTime in Timesheets tbl
strInvoiceMethod = Me.Controls("InvoiceMethod").Value 'this is InvoiceMethod from Jobs tbl
blnIsOperational = Me.Controls("IsOperational").Value 'this is IsOperational from Timesheets tbl
strOpCode = Me.Controls("OperationCode").Value  'this is OperationCode from Timesheets tbl

'we want to round up to the next integer
'this converts to integer
myint = mynum

'used to correct for a decimal less than .5 because it rounds down
If myint < mynum Then
 myint = myint + 1
End If

'this gets the decimal part of the hours in ActualTime
remainder = 1 - (myint - mynum)

If strInvoiceMethod = "Hourly" And blnIsOperational = -1 And strOpCode <> 100 Then
    If remainder > 0 And remainder <= 0.25 Then
        rounded = 0.25
        If remainder > 0.25 And remainder <= 0.375 Then
            rounded = 0.25
            If remainder > 0.375 And remainder <= 0.5 Then
                rounded = 0.5
                If remainder > 0.5 And remainder <= 0.625 Then
                    rounded = 0.5
                    If remainder > 0.625 And remainder <= 0.75 Then
                        rounded = 0.75
                        If remainder > 0.75 And remainder <= 0.875 Then
                            rounded = 0.75
                            If remainder > 0.875 And remainder <= 1 Then
                                rounded = 1
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If

    'readjusts the integer so just add the rounded part
    myint = myint - 1

    'add the rounded plus the integer part of ActualTime
    result = rounded + myint
    Me.Controls("RoundedTime").Value = result
    RoundedTime = ActualTime
End If
End Sub

How can I incorporate this code into the SQL Update statement?

Best regards,
W Delaney

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 19 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros