Listers ...
I found this discussion very interesting.
In the past, I encountered a similar situation and would write an on-enter procedure to the hours worked control and then do a lookup to get the rate if the hour worked value was null or ""
Something like this
If IsNull([txtHoursWorked]) Or [txtHoursWorked] = "" Then
txtRate.Value = DLookup("[Rate]", "tblRates", "tblRates!EffectiveRateDate > [Forms]![frmMyForm]!txtProjectDate")
Maybe not the best way, but it worked
Patty
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Jim-
>
> You need day-by-day detail to be able to calculate correctly. If all you have is project start / end and total hours, it's impossible to calculate the correct rate. What does your table look like that has hours in it?
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of lancucki
> Sent: Monday, June 17, 2013 11:19 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Project and tasks cost question
>
> In that case, you would break the results into a series of intervals based on the effective dates and use the rates for each interval. A bit more work, but you still use the original table. This is the basis for handling historical data.
>
> John…
>
> From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Jim Wagner
> Sent: Monday, June 17, 2013 3:53 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Project and tasks cost question
>
>
> John,
>
> What happens if the project or task spans over the dates? will it calculate both pay rates?
>
> Jim Wagner
> ________________________________
>
> ________________________________
> From: Jim Wagner < <mailto:luvmymelody%40yahoo.com> luvmymelody@...>
> To: " <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com" < <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com>
> Sent: Monday, June 17, 2013 8:04 AM
> Subject: Re: [MS_AccessPros] Project and tasks cost question
>
>
>
> John,
>
> State University with the state broke, No I did not expect a raise.
>
> I will try the suggestion and let you know
>
> Jim Wagner
> ________________________________
>
> ________________________________
> From: John Viescas < <mailto:JohnV%40msn.com> JohnV@...>
> To: <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com
> Sent: Monday, June 17, 2013 7:57 AM
> Subject: RE: [MS_AccessPros] Project and tasks cost question
>
>
> Jim-
>
> Congrats! What, you never expected to get a raise???
>
> John's suggestion is good. With that, write a little public function like
> this:
>
> Option Compare Database
> Option Explicit
>
> Dim intRateCount As Integer
> Dim curRates() As Currency
> Dim datEffDates() As Date
>
> Public Function GetRate(datTransDate As Date) As Currency
> 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 datTransDate >= 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
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> <http://www.viescas.com/> http://www.viescas.com/
> (Paris, France)
>
> -----Original Message-----
> From: <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com
> [mailto: <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com] On Behalf Of lancucki
> Sent: Monday, June 17, 2013 4:29 PM
> To: <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Project and tasks cost question
>
> Create a table for the rates that include an effective date. Anything before
> the effective date will use the base rate, anything after with use the rate
> based on the effective date. So if the rates change, you can add a new
> record with the new effective date and rate.
>
> John. Visio MVP
>
> From: <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com
> [mailto: <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com] On Behalf Of luvmymelody
> Sent: Monday, June 17, 2013 10:00 AM
> To: <mailto:MS_Access_Professionals%40yahoogroups.com> MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Project and tasks cost question
>
> Hello all,
>
> Well it finally happened, I will be getting a raise after 6 years next
> month. The problem is that my tasks and project database has been using a
> calculation on the forms and reports for calculating the costs for the
> project time. Though I want to change the cost on the projects, I only have
> the calculations in expressions on the forms and reports not in a table. I
> can change the expression to the new rate but that will change the costs for
> all previous work to the new rate. This will not be accurate because all
> previous work will have been done at the lower rate.
>
> How can I rework the database to keep the lower rate for work already
> completed and still working on and any new projects and tasks use the new
> rate?
>
> Thank You
>
> Jim Wagner
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
> [Non-text portions of this message have been removed]
>
> [Non-text portions of this message have been removed]
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (17) |
Tidak ada komentar:
Posting Komentar