Query Formula

  • Thread starter Thread starter Red
  • Start date Start date
R

Red

I'm stuck on a what would seem a very simple function. I want to create a
field using the Design view of Query as my SQL skills are non-existent. I'd
appreciate if someone could help translate the below formula

In lay terms

if ContTD=0 then
ContTD=0
else if ContTD<0 then
ConTD=(-[ContTD])/[FeesEarned]*-1
else [ContTD]/[FeesEarned]

Thanks in advance.
 
Dear Red:

You can calculate this within your query using IIf() functions:

IIf(ContTD = 0, 0, IIf(ContTD < 0, -ContTD / FeesEarned * -1, ContTD /
FeesEarned))

I can't help but notice that, in this second contition (ContTD < 0)
the two results are IDENTICAL. That is:

-[ContTD])/[FeesEarned]*-1

is a double negative, and it is the same as:

[ContTD])/[FeesEarned]

Also, if what you meant was to have:

-[ContTD])/[FeesEarned]

that is, to make it positive when it is negative, then ABS(), the
absolute values, would do this for you. That makes the whole thing
quite a bit simpler:

IIf(ContTD = 0, 0, ABS(ContTD / FeesEarned))

Finally, for all values where FeesEarned <> 0 (so you don't divide by
zero), and where ContTD is 0, then ContTD / FeesEarned would be zero,
so an additional simplification would be:

ABS(ContTD / FeesEarned)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top