These look like business calculations with hard-coded values like 7, 2/3, 617, 463, 0.75, .... If this is the case, I would create a module in Access named "modBusinessCalcs" and then add functions. When the 617 changes to 618 or the 463 changes to 495 you can edit the module rather than search for every place you have entered the numbers. An even better solution might be to store the numbers in a table so you can edit/maintain data rather than code.
You can also add comments plus the logic is much easier to follow in code rather than an expression.
Guessing at data types etc. the first function might be:
Public Function GetEndDate(dblC25 As Double, datC20 As Date) As Variant
'need to duplicate
'=IF(C25=0," ",(C20+(C25*7))-1)
Dim intMultiplier As Integer
Dim intSub As Integer
intMultiplier = 7
intSub = 1
'=========== no hard-coded values below this line ===========
If dblC25 = 0 Then
GetEndDate = Null
Else
GetEndDate = (datC20 + (dblC25 * intMultiplier)) - intSub
End If
End Function
You would want to change the C25 and C20 to something more descriptive.
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: joh344@aol.com
Date: Mon, 21 Apr 2014 08:09:11 -0700
Subject: [MS_AccessPros] Formulas
I need help with some formulas in Access.
1. Here's the formula I use in Excel to calculate the end date: =IF(C25=0," ",(C20+(C25*7))-1). I need a formula like this in Access to calculate the end date. I have a start date column and PPD weeks column. I need to calculate the end date with the start date column + PPD weeks column.
2. Here's the formula I use in Excel to calculate TTD Rate: =IF(B61*2/3>617,617, B61*2/3). I need a formula like this in Access. I need the calculated number to be rounded, too. I also need to add a condition that if DOI column is 01/01/14 use 617, if DOI column is 01/01/13 use 602, if DOI column is 01/01/12 use 584, if DOI column is 01/01/11 use 575, if DOI column is 01/01/10 use 562, if DOI column is 01/01/09 use 550.
3. Here's the formula I use in Excel to calculate PPD Rate: =IF(E62>205.34,IF((0.75*E62)<463,ROUND(0.75*E62, 0),463), IF(2/3*B61<154, ROUND(2/3*B61, 0), 154)). I need a formula like this in Access. I also need to add a condition that if DOI column is 01/01/14 use 463, if DOI column is 01/01/13 use 452, if DOI column is 01/01/12 use 438, if DOI column is 01/01/11 use 431, if DOI column is 01/01/10 use 422, if DOI column is 01/01/09 use 413.
4. I need a formula that takes the TTD Rate column (which is a weekly rate) x ttdweeks column x ttddays column = Total TTD paid.
Thanks for your help.
Jeremy
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (22) |
Tidak ada komentar:
Posting Komentar