Formulas

[June7]

Not going to try to interpret the Excel referencing to your data structure. Provide your attempts for analysis same as we just did.

[joh344]

I have an AWW column (average weekly wage column), TTD Rate column (weekly TTD Rate), and PPD Rate column (weekly PPD Rate column). I need to calculate the TTD Rate from the number that's in the AWW column. TTD Rate is 66 2/3% of the AWW, rounded to the nearest whole dollar, up to 584.

TTD Rate Formula
Here's the formula: =IF(AWW*2/3>617,617,AWW*2/3). Basically, the TTD Rate is 66 2/3% of the AWW, rounded to the nearest whole dollar, up to 617 for 2014 year. I need to put the rounded to the nearest whole dollar in this formula. The TTD rate has a min and max. The max value changes every year. The min value (20) stays the same every year. I also need put the min value in the formula. Since the max rate changes yearly, I need to add a condition to the formula that says if DOI (date of injury) 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, etc.

Thanks.

[June7]

I suggest you create a table of TTD rates with fields: EffYear, Rate.

Then options:

1. build a query of the data table to calculate a year from the DOI value and join the Rates table to the query linking on the two year fields

2. use DLookup domain aggregate function to pull the appropriate Rate based on the year of DOI value, this calc can be in query or textbox or in code

[joh344]

I need to add 2 different fields: TTD Amt field and PPD Amt field. Here's what I did:

Grand Total: Nz(TTD Amt) + Nz(PPD Amt). I get an error that says: The expression you entered contains invalid syntax. Please help.

[June7]

Field names have spaces. Must enclose in [].

Should avoid spaces and special characters/punctuation (underscore is exception) in naming convention. Also don't use reserved words as field names.

[joh344]

Okay. I'm trying to add 2 values in TTD Amt field and PPD Amt field. It's not returning anything. Here's the formula that I"m using: Grant Total: [TTD Amt]+[PPD Amt]. When I run it, it doesn't show anything in the Grand Total field. It's blank.

[joh344]

Got it to work. Never mind. Way too sensitive. LOL.

[joh344]

Well, I thought I had it worked out. Here's the formula I'm using:
Grant Total: [TTD Amt]+[TPD Amt]+[PPD Amt]+[PTD Amt]+[Death Amt]+[Lump Sum Amt]+[JP Amt]+[CAtty Fee]+[DAtty Fee]+[Other Comp]+[Hospital Amt]+[Medical Amt]+[Drug Amt]+[Funeral Amt]+[Rehab Amt]+[Other (Expense) Amt]. It's not returning anything. I'm just trying to add all these fields together to get a grand total. Please help.

[June7]

See post 14. Handle possible Null.

[joh344]

Here's the formula I'm using: Grant Total: Nz([TTD Amt]+[TPD Amt]+[PPD Amt]+[PTD Amt]+[Death Amt]+[Lump Sum Amt]+[JP Amt]+[CAtty Fee]+[DAtty Fee]+[Other Comp]+[Hospital Amt]+[Medical Amt]+[Drug Amt]+[Funeral Amt]+[Rehab Amt]+[Other (Expense) Amt])

I still don't get a result. What's wrong with the formula?

[joh344]

Changed the formula to this: Grand Total: Nz([TTD Amt])+Nz([TPD Amt])+Nz([PPD Amt])+Nz([PTD Amt])+Nz([Death Amt])+Nz([Lump Sum Amt])+Nz([JP Amt])+Nz([CAtty Fee])+Nz([DAtty Fee])+Nz([Other Comp])+Nz([Hospital Amt])+Nz([Medical Amt])+Nz([Drug Amt])+Nz([Funeral Amt])+Nz([Rehab Amt])+Nz([Other Expense Amt]). Now I get a result, but I don't get a result in currency form. Grand Total is not in my table. So, I don't know how to change the number format to currency.

[June7]

Set the Format property of textbox on form or report.

[joh344]

It's not in a form or a report. It's in a query under (create query by using wizard). Thanks.

[June7]

Why does it matter to show as currency in query? Users should not interact with tables and queries, only forms and reports.

Only option in query is to use Format function which results in a text value.

[joh344]

I've created a form. How do I change the format to currency for grand total in the form? I need a step-by-step process.