Re: [MS_AccessPros] Quantity per hour calculaton

 

=[Hours]*hourly rate

if the hourly rate is going to change?

then use this

=[txtProjectTotal]*GetRate([TaskDate])

create a table called tblRates with these fields

EffDate, Rate

the date is the date the rate starts and the Rate is the hourly rate like below

1/3/2013  20.00

1/3/2014  30.00

1/3/2015  40.00

add this to a module. Mr Viescas sent this to me. 

'John 's suggestion is good. With that, write a little public function like

'this:

Public Function GetRate(TaskDate As Date) As Currency

Dim intRateCount As Integer

Dim curRates() As Currency

Dim datEffDates() As Date

Dim db As DAO.Database, rst As DAO.Recordset, intI As Integer

' If rates table not loaded,

If intRateCount = 0 Then

' Point to this database

Set db = CurrentDb

' Open the rates table

Set rst = db.OpenRecordset("SELECT * FROM tblRates " & _

"Order By EffDate Desc;")

' Loop to load all the rates in descending date order

Do Until rst.EOF

' Add 1 to the number of rates

intRateCount = intRateCount + 1

' Expand the two arrays

ReDim Preserve curRates(intRateCount)

ReDim Preserve datEffDates(intRateCount)

' Load the data

curRates(intRateCount) = rst!Rate

datEffDates(intRateCount) = rst!EffDate

' Get the next record and loop

rst.MoveNext

Loop

' Close out the recordset

rst.Close

Set rst = Nothing

Set db = Nothing

End If

' Now find the rate

For intI = 1 To intRateCount

If TaskDate >= datEffDates(intI) Then

' Found it!

GetRate = curRates(intI)

Exit For

End If

Next intI

' Done

End Function

'tblRates needs a Rate and an EffDate column. First record will have a date

'like 1/1/1900 and your current rate. Second record will have the date your

'raise is effective and the new rate. Future raises just need another row in

'the Table!

'

'Everywhere in your app where you have hard coded the rate, substitute a call

'to the GetRate function. Note that the function loads the rate table the

'first time it is called, then uses the loaded array to quickly return the

'rate thereafter.

'

'Have fun...

'

'John Viescas, Author

 

Jim Wagner

---

On Tuesday, November 12, 2013 8:00 AM, "bradgriffis@yahoo.com" <bradgriffis@yahoo.com> wrote:

 

Hello, how do I calculate the quantity per hour in a database that records the total quantity and total minutes for a specific task? I want the value to appear in a report. I use Access 2007. Thanks.

Brad

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (3)

Recent Activity:

• New Members 5

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___