troubleshooting Question

W D asked on

Hi,

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

Else

If remainder > 0.25 And remainder <= 0.375 Then

rounded = 0.25

Else

If remainder > 0.375 And remainder <= 0.5 Then

rounded = 0.5

Else

If remainder > 0.5 And remainder <= 0.625 Then

rounded = 0.5

Else

If remainder > 0.625 And remainder <= 0.75 Then

rounded = 0.75

Else

If remainder > 0.75 And remainder <= 0.875 Then

rounded = 0.75

Else

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

Else

RoundedTime = ActualTime

End If

End Sub

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

Best regards,

W Delaney

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

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

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").

strInvoiceMethod = Me.Controls("InvoiceMethod

blnIsOperational = Me.Controls("IsOperational

strOpCode = Me.Controls("OperationCode

'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

Else

If remainder > 0.25 And remainder <= 0.375 Then

rounded = 0.25

Else

If remainder > 0.375 And remainder <= 0.5 Then

rounded = 0.5

Else

If remainder > 0.5 And remainder <= 0.625 Then

rounded = 0.5

Else

If remainder > 0.625 And remainder <= 0.75 Then

rounded = 0.75

Else

If remainder > 0.75 And remainder <= 0.875 Then

rounded = 0.75

Else

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")

Else

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.

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.