Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by ConnectWise

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sSQL = "UPDATE Jobs, Timesheets Set Timesheets.RoundedTime = " & Controls("RoundedTime").Va

sSQL = sSQL & "WHERE Jobs.JobID = Timesheets.JobID AND Jobs.InvoiceMethod = 'Hourly' AND Timesheets.IsOperational AND Timesheets.OperationCode <> 100"

Does this mean that RoundedTime is being set to the present value of what's in the form textbox for RoundedTime?

I don't think that's what I need. If the value of the job InvoiceMethod changes from PerUnit to Hourly in the spreadsheet (other criteria are considered too, but I'll make this example simple) and the macro button is clicked, I need RoundedTime to update in the db based upon what ActualTime is and round to the nearest quarter hour accordingly. If RoundedTime is the same as ActualTime already in the db, I don't understand how setting RoundedTime to the present value of what's in the form textbox helps....

sSQL = sSQL & "WHERE Jobs.JobID = Timesheets.JobID AND Jobs.InvoiceMethod = 'Hourly' AND Timesheets.IsOperational AND Timesheets.OperationCode <> 100"

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialcase when Timesheets.ActualTime - cast(Timesheets.ActualTime

What this does is takes the ActualTime (6.12 for example) and subtracts the integer portion of it (6) to get the remainder (.12). That is what is then used to determine how to round.

So we do:

cast(Timesheets.ActualTime

Because we want to take the integer portion of the number and add the rounded amount to it. So if we have 6.39, the rounded portion is going to calculate to .5. We want to add that to the integer portion, 6, to get a total of 6.5

Make sense?

Access doesn't seem to like the word cast, so I'm researching the internet now for what to use....

UPDATE Jobs, Timesheets

Set Timesheets.RoundedTime = CInt(Timesheets.ActualTime

End

WHERE Jobs.JobID = Timesheets.JobID AND Jobs.InvoiceMethod = 'Hourly' AND Timesheets.IsOperational AND Timesheets.OperationCode <> 100

Now, Access is having a problem with the CASE WHEN statement "Set Timesheets.RoundedTime = CInt(Timesheets.ActualTime

I'll research Access and CASE statements on the internet...

I've got:

Update Jobs, Timesheets

Set Timesheets.RoundedTime =

Iif (Timesheets.ActualTime - CInt(Timesheets.ActualTime

IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime

IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime

IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime

IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime

IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime

IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime

IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime

)))))))

WHERE Jobs.JobID = Timesheets.JobID AND Jobs.InvoiceMethod = 'Hourly' AND Timesheets.IsOperational AND Timesheets.OperationCode <> 100

Trouble is, when I've got something like 2.08 for ActualTime, RoundedTime becomes .25 and not 2.25.

UPDATE Timesheets As t INNER JOIN Jobs As j ON j.JobID = t.JobID

Set t.RoundedTime = Int(t.ActualTime) +

IIf (t.ActualTime - Int(t.ActualTime) = 0, 0,

IIf (t.ActualTime - Int(t.ActualTime) <= .375, .25,

IIf (t.ActualTime - Int(t.ActualTime) <=.625, .5,

IIf (t.ActualTime - Int(t.ActualTime) <= .875, .75,

IIf (t.ActualTime - Int(t.ActualTime) <= 1, 1)))))

WHERE j.InvoiceMethod = 'Hourly' AND t.IsOperational AND t.OperationCode <> 100

or this works too:

Update Jobs, Timesheets

Set Timesheets.RoundedTime = CInt(Timesheets.ActualTime

Iif (Timesheets.ActualTime - CInt(Timesheets.ActualTime

IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime

IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime

IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime

IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime

IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime

IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime

IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime

)))))))

WHERE Jobs.JobID = Timesheets.JobID AND Jobs.InvoiceMethod = 'Hourly' AND Timesheets.IsOperational AND Timesheets.OperationCode <> 100

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by ConnectWise

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

sSQL = "UPDATE Jobs, Timesheets Set Timesheets.RoundedTime = " & Me.Controls("RoundedTime")

sSQL = sSQL & "WHERE Jobs.JobID = Timesheets.JobID AND Jobs.InvoiceMethod = 'Hourly' AND Timesheets.IsOperational AND Timesheets.OperationCode <> 100"