• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 539
  • Last Modified:

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

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





0
wdelaney05
Asked:
wdelaney05
  • 11
  • 8
1 Solution
 
derekkrommCommented:
are you able to just do:

sSQL = "UPDATE Jobs, Timesheets Set Timesheets.RoundedTime = " & Me.Controls("RoundedTime").Value
    sSQL = sSQL & "WHERE Jobs.JobID = Timesheets.JobID AND Jobs.InvoiceMethod = 'Hourly' AND Timesheets.IsOperational AND Timesheets.OperationCode <> 100"

0
 
wdelaney05Author Commented:
I keep getting the err msg: Invalid use of Me keyword  
0
 
derekkrommCommented:
how about:

sSQL = "UPDATE Jobs, Timesheets Set Timesheets.RoundedTime = " & Controls("RoundedTime").Value
    sSQL = sSQL & "WHERE Jobs.JobID = Timesheets.JobID AND Jobs.InvoiceMethod = 'Hourly' AND Timesheets.IsOperational AND Timesheets.OperationCode <> 100"
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
wdelaney05Author Commented:
Re: Set Timesheets.RoundedTime = " & Controls("RoundedTime").Value
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....
0
 
wdelaney05Author Commented:
I think perhaps I need more sophisticated SQL in my Update statement. I was looking on the internet and perhaps I need a series of If statements or CASE statements....
0
 
derekkrommCommented:
sorry, i misunderstood the original question. basically you want the functionality of the IF statements within your SQL update?
0
 
wdelaney05Author Commented:
Yes, please, that would be grand, thanks much.
0
 
derekkrommCommented:
sSQL = "UPDATE Jobs, Timesheets Set Timesheets.RoundedTime = cast(Timesheets.ActualTime as int) + case when Timesheets.ActualTime - cast(Timesheets.ActualTime as int) = 0 then 0  when Timesheets.ActualTime - cast(Timesheets.ActualTime as int) <= .375 then .25 when Timesheets.ActualTime - cast(Timesheets.ActualTime as int) <= .625 then .5 when Timesheets.ActualTime - cast(Timesheets.ActualTime as int) <= .875 then .75 when Timesheets.ActualTime - cast(Timesheets.ActualTime as int) < 1 then 1 end"

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

0
 
wdelaney05Author Commented:
Both ActualTime and RoundedTime have the datatype of Double in the Access db. Is casting them as Ints necessary?
0
 
derekkrommCommented:
Yes, the way it works is as follows:

case when Timesheets.ActualTime - cast(Timesheets.ActualTime as int) = 0 then 0

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 as int) + case ...

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?
0
 
wdelaney05Author Commented:
Yup, makes sense now.
Access doesn't seem to like the word cast, so I'm researching the internet now for what to use....
0
 
derekkrommCommented:
Oh, in access its probably

int(Timesheets.ActualTime)
0
 
wdelaney05Author Commented:
ok. I did this after some internet research:
UPDATE Jobs, Timesheets
Set Timesheets.RoundedTime = CInt(Timesheets.ActualTime) + case when Timesheets.ActualTime  CInt(Timesheets.ActualTime) = 0 then 0  when Timesheets.ActualTime - CInt(Timesheets.ActualTime ) <= .375 then .25 when Timesheets.ActualTime - CInt(Timesheets.ActualTime ) <= .625 then .5 when Timesheets.ActualTime - CInt(Timesheets.ActualTime ) <= .875 then .75 when Timesheets.ActualTime - CInt(Timesheets.ActualTime ) < 1 then 1
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) + case when....". It highlights the word when...
I'll research Access and CASE statements on the internet...
0
 
derekkrommCommented:
Ya, sorry, I wrote that for SQL Server.

I think access has a "Select Case "
0
 
wdelaney05Author Commented:
Ok, I guess Access doesn't use CASE statements, I'm not sure about that. Access uses IIf (Immediate If) instead.
I've got:
Update Jobs, Timesheets
Set Timesheets.RoundedTime =
Iif  (Timesheets.ActualTime - CInt(Timesheets.ActualTime) = 0, 0,
IIf  (Timesheets.ActualTime - CInt(Timesheets.ActualTime )  > 0 And Timesheets.ActualTime -      CInt(Timesheets.ActualTime ) <= .25,  .25,
IIf  (Timesheets.ActualTime - CInt(Timesheets.ActualTime ) > .25 and Timesheets.ActualTime - CInt(Timesheets.ActualTime )  <= .375, .25,
IIf  (Timesheets.ActualTime - CInt(Timesheets.ActualTime ) > .375 and Timesheets.ActualTime - CInt(Timesheets.ActualTime ) <=.5,  .5,
IIf  (Timesheets.ActualTime - CInt(Timesheets.ActualTime ) > .5 and Timesheets.ActualTime - CInt(Timesheets.ActualTime ) <=.625, .5,
IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime ) > .625 and Timesheets.ActualTime - CInt(Timesheets.ActualTime ) <=.75, .75,
IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime ) > .75 and Timesheets.ActualTime - CInt(Timesheets.ActualTime ) <= .875, .75,
IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime ) > .875 and Timesheets.ActualTime - CInt(Timesheets.ActualTime ) <= 1, 1, 2)
)))))))
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.
0
 
wdelaney05Author Commented:
I thought CInt takes the ActualTime (6.12 or 2.08 for example) and subtracts the integer portion of it (6) to get the remainder (.12) or (2) to get the remainder of (.08). That is what is then used to determine how to round.
0
 
wdelaney05Author Commented:
Ok, After much testing, research, asking other EE experts, etc. here's what I have:
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 - 0.49) +
Iif  (Timesheets.ActualTime - CInt(Timesheets.ActualTime- 0.49) = 0, 0,
IIf  (Timesheets.ActualTime - CInt(Timesheets.ActualTime - 0.49)  > 0 And Timesheets.ActualTime -      CInt(Timesheets.ActualTime - 0.49) <= .25,  .25,
IIf  (Timesheets.ActualTime - CInt(Timesheets.ActualTime - 0.49) > .25 and Timesheets.ActualTime - CInt(Timesheets.ActualTime - 0.49)  <= .375, .25,
IIf  (Timesheets.ActualTime - CInt(Timesheets.ActualTime - 0.49) > .375 and Timesheets.ActualTime - CInt(Timesheets.ActualTime - 0.49) <=.5,  .5,
IIf  (Timesheets.ActualTime - CInt(Timesheets.ActualTime- 0.49 ) > .5 and Timesheets.ActualTime - CInt(Timesheets.ActualTime - 0.49) <=.625, .5,
IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime - 0.49) > .625 and Timesheets.ActualTime - CInt(Timesheets.ActualTime - 0.49) <=.75, .75,
IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime - 0.49) > .75 and Timesheets.ActualTime - CInt(Timesheets.ActualTime - 0.49) <= .875, .75,
IIf (Timesheets.ActualTime - CInt(Timesheets.ActualTime - 0.49) > .875 and Timesheets.ActualTime - CInt(Timesheets.ActualTime - 0.49) <= 1, 1, 1)
)))))))
WHERE Jobs.JobID = Timesheets.JobID AND Jobs.InvoiceMethod = 'Hourly' AND Timesheets.IsOperational AND Timesheets.OperationCode <> 100

0
 
derekkrommCommented:
Are you good to go on this? Seems like your latest query should work. Sorry if I couldn't provide enough assistance.
0
 
wdelaney05Author Commented:
Yep, I'm good to go, sorry, I forgot to close the question.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now