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"

Solved

Posted on 2007-07-19

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

19 Comments

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"

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"

case 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

Title | # Comments | Views | Activity |
---|---|---|---|

Sql Query Lookup based on range | 2 | 17 | |

MS Access 2010 - Error 53-File Not Found | 27 | 28 | |

.xltm file opens as .xlsx file | 3 | 24 | |

Best way to create a 3 deep master/child Access form | 4 | 13 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**15** Experts available now in Live!