Formulas

[joh344]

I need help with some formulas in Access.



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.

Here’s the formula I use in Excel to calculate TTD Rate: =IF(B61*2/3>617,617, B61*2/3). 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.

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 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.

I need a formula to that takes the TTD Rate column x ttdweeks column x ttddays column = TTD Total paid.

Thanks,
Jeremy

[June7]

Use DateAdd() and DateDiff() for date calcs. Other date functions are Date(), Now(), Year(), Month(), Weekday().

Use IIf() for conditional expressions.

Access Help has specifics or search web.

Be aware, Round acts differently in Access than Excel. In Access it uses even/odd rule. Example:

Round(3.45,1) = 3.4
Round(3.46,1) = 3.5
Round(3.35,1) = 3.4

[joh344]

Thanks. I'm having trouble with this formula in Access: [TTD_Rate]*([ttdwks]+([ttddys]\7)). When I enter it in Access, it tells me to enter the parameter of TTD Rate. I don't understand what that means. What I'm trying to accomplish with this formula is take the TTD Rate column (a weekly rate) x ttdwks columns (ttd weeks) + ttddys column (ttd days). Thanks for any help.

[June7]

Access can't find a field named TTD_Rate so it triggers a popup for input of value. Does the field actually have an underscore in its name?

[joh344]

No. There is no underscore.

[joh344]

I corrected it. And, it worked. Thanks. I'm going to need help with the other formulas though.

[joh344]

Okay. I'm almost there. [TTD_Rate]*([ttdwks]+([ttddys]\7)). What I'm trying to accomplish with this formula is take the TTD Rate column (a weekly rate) x ttdwks columns (ttd weeks) + ttddys column (ttd days). All this formula does is takes the TTD Rate x TTD weeks column. It doesn't acknowledge the ttddys column.

[June7]

What does 'doesn't acknowledge' mean? Algebraic rules apply. Hierarchy of operators and parens control the order of operations. The inner expression calcs first.

[joh344]

What I mean is that it doesn't take the TTD Rate column (a weekly rate) x ttddys column (number of TTD days)? Somewhere in the formula I have to divide the TTD Rate by 7 to get a daily rate, so it will calculate how much of paid in TTD days. As you can see, I'm not good with formulas.

[June7]

Apply parens as you see fit or none. Maybe:

[TTD Rate] / 7 * ttddys

Which means:

weekly rate divided by 7 then multiplied by number of days

What is ttdwks for?

[joh344]

That doesn't work. I'm trying to calculate the amount of TTD paid using 3 columns: TTD Rate (weekly TTD rate), ttdwks (number of weeks on TTD), and ttddys (number of days on TTD).

[June7]

Is that number of days per week, not continuous days? Then just multiply by ttdwks

[TTD Rate] / 7 * ttddys * ttdwks

[joh344]

The number of days is not continuous. In other words, the ttdwks column is not equal to the ttddys column. If the ttdwks column is 7 weeks, then the ttddys column is 0. If the ttdwks column is 7 weeks and 1 day, then the ttddys column is 1 day. Thanks for your help.

[June7]

Well, that complicates. Problem is if any field is null the result will be null. Arithmetic with null results in null. Handle Null with Nz().

[TTD Rate] * (Nz(ttddys,0) / 7 + Nz(ttdwks,0))

[joh344]

Thanks. It worked. If you don't mind, I'll need some help with the other formulas.