IIF statement in Expression on Form

[GaryReno]

I am building a database that is for a writers payroll. I'm stuck.

I have a form from a table that has article information and word counts. I need a form to do some functions.

LongWordCount and ShortWordCounts is where the TotalPay gets its information.

So i need an IIF statement to say If LongWordCount has a value and ShortWordCount is Null do this math =0.02*([LongWordCount]-1500)+65

If LongWordCount IsNull look in ShortWordCount for a value and then do this math =IF([ShortWordCount]<800,35,0.02*([ShortWordCount]-800)+35)

So a writer writes a long word count article and gets paid 2 cents for everything over 1500 words plus $65.00

If they write a short word count and its less than 800 words they get a flat $35.00 if its over 800 words they get $35.00 plus 2 cents a word.

HELP PLEASE

[June7]

LongWordCount and ShortWordCount are number fields?

Do calculations in query:

LWC: 65 + 0.02 * ([LongWordCount] - IIf([LongWordCount] > 1500, 1500, 0))

SWC: 35 + 0.02 * ([ShortWordCount] - IIf([ShortWordCount] > 800, 800, 0))

If field is Null the result will be Null.

Now use those calculated fields in another expression.

In a textbox on form: Nz(LWC, SWC)

Or one expression:

IIf([LongWordCount] Is Null, 35, 65) + 0.02 * (Nz([LongWordCount], [ShortWordCount]) - IIf(Nz([LongWordCount, ShortWordCount) > IIf([LongWordCount] Is Null, 1500, 800), IIf([LongWordCount] Is Null, 1500, 800), 0))

Regarding use of Is Null, IsNull, Nz in query - review http://allenbrowne.com/QueryPerfIssue.html


Otherwise, build a custom VBA function to do calcs and return appropriate value.