Help - Decypher this formula

  • Thread starter Thread starter Dazed And Confused
  • Start date Start date
D

Dazed And Confused

We ran across a formula in a query that someone else wrote that we are
embarassed to admit we just do not understand. We tried to find
something on Google, but didn't know how to phrase the search. Any
help anyone could give to help understand what this formula is doing
and how it works would be so appreciated.

Here goes: This formula is in the "Field:" row in a query:
IIf([NewFlag]=-1,(Int((((([rate]*1)*12)/26)*100)+0.5)/100)

Thank you!
Mike and Jeff
 
Well guys, it's hard to say out of context. the ([rate]*1) bit seems to
indicate that this person has no idea what they're doing.

Try to figure out what the query is trying to achieve. What is [NewFlag]
for? This abomination is evidently trying to normalize [rate] for something,
but what? (If you do the algebra out, the formula becomes [(2/13)R+.005] --
not useful)

The 12 in the formula could be months/year, 26 could be weeks/6 mo. All I
can really suggest is to figure out why the different terms are there, and
what [NewFlag] is for. If you can find out when this mess is supposed to
run, you can probably decypher why it's running.

Good Luck.
 
Formulae usually have some kind of context in which they are used. What's
the context?

Regards

Jeff Boyce
Microsoft Office/Access MVP

(P.S., multiplying time 12 might be # of months in a year; dividing by 26
might be to get 2-weekly values; adding .5 and dividing by a 100 might be
related to rounding; ... all of these are wild inferences based on NO
context!)
 
Looks as if someone is taking a monthly rate
Multiplying that by 12 (number of months)
Dividing by 26 (two week periods in a year)
Stripping off any decimal portion
Adding a little bit (.5) to make sure rounding takes place in the desired
direction
Then rounding that to two decimal places.

This type of calculation might be used in a payroll system to figure out the
deduction amount to pay insurance that has a monthly payment.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Dazed And Confused said:
Here goes: This formula is in the "Field:" row in a query:
IIf([NewFlag]=-1,(Int((((([rate]*1)*12)/26)*100)+0.5)/100)
Click to expand...

You're missing the third argument on the IIF statement. Otherwise
I'd agree with Jeff and John.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Thank you all for your help. We apologize, we accidently left out the
final part - we think - it would be "Null" as in: IIf([NewFlag]=-1,
(Int((((([rate]*1)*12)/26)*100)+0.5)/100),NULL).
*
We "think" this has to do with some kind of payroll deduction
calculation - United Way maybe? And we don't know what the "NewFlag"
is. the reason we don't know for sure is because the database holding
this has about about a zillion strangely-named queries in it and no
documentation as to what they are supposed to do - or even if they are
still being used.
*
Again - thank you.
Mike & Jeff
 
?Context? (without some idea of the arena you are working in, it's a bit
like asking "what does this little dohickey do?")

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you all for your help. We apologize, we accidently left out the
final part - we think - it would be "Null" as in: IIf([NewFlag]=-1,
(Int((((([rate]*1)*12)/26)*100)+0.5)/100),NULL).
*
We "think" this has to do with some kind of payroll deduction
calculation - United Way maybe? And we don't know what the "NewFlag"
is. the reason we don't know for sure is because the database holding
this has about about a zillion strangely-named queries in it and no
documentation as to what they are supposed to do - or even if they are
still being used.
*
Again - thank you.
Mike & Jeff
 
the reason we don't know for sure is because the database holding
this has about about a zillion strangely-named queries in it and no
documentation as to what they are supposed to do - or even if they are
still being used.
Click to expand...

You might want to invest in a tool such as Rick Warren's Find and Replace or
the very capable (but NOT inexpensive) Total Access Analyzer. These can track
down the crossreferences and let you know where each query is used and how.

Free: http://www3.bc.sympatico.ca/starthere/findandreplace
Find and Replace: http://www.rickworld.com
Speed Ferret: http://www.moshannon.com
Total Access Analyzer: http://www.fmsinc.com
 
Multiplying by 1 makes no sense at all, since it will give you what you
started with. Adding .5 to the result and wrapping it in the Int function
seems to be serving to round the number, but in that case you may as well
use the Round function. NewFlag may be a Yes/No field, where the -1 equals
True and 0 equals false. It may be possible to reduce this expression to
something like:
IIf([NewFlag]=True,Round(([rate]*12)/26),Null)

Actually, I think it could be:
IIf([NewFlag]=True,Round([rate]*12/26),Null)

You could try putting the new formula in there, and seeing if it gives the
same result as the old one.

I agree that this sounds like something that is coming up with a bi-weekly
value, but I also agree that without context there is not much more to say.
You could try finding a form or report that is bound to this query, and
seeing if the calculated field is being used as a control source for a text
box (most likely, I think). With any luck the text box has a label that
will explain something.

Diagnostic tools, as suggested, may be a good choice.

Thank you all for your help. We apologize, we accidently left out the
final part - we think - it would be "Null" as in: IIf([NewFlag]=-1,
(Int((((([rate]*1)*12)/26)*100)+0.5)/100),NULL).
*
We "think" this has to do with some kind of payroll deduction
calculation - United Way maybe? And we don't know what the "NewFlag"
is. the reason we don't know for sure is because the database holding
this has about about a zillion strangely-named queries in it and no
documentation as to what they are supposed to do - or even if they are
still being used.
*
Again - thank you.
Mike & Jeff
 
Back
Top