Need help writing a workable formula in form design......

[Chartswife]

Im running vista, microsoft office pro 2007

I'm trying to write a workable formula in an unbound text in form design.
It's a monster formula.
Their are 4 components:

comp/att*100-30/20
yds/att-3*.25
td/att*20
2.375-int/att*25

Each one of the answers for these 4 components must be checked , if greater
than 2.375 then set to 2.375 and if less than 0 then set to 0

and finally, all added together, multiplied by 100 and divided by 6

This is what I have but I get Syntax Error. I'm very new to access, so
please keep this in mind, thank you.

=PasserRating[comp]/[Att]*100-30/20+iif(>2.375=2.375),iif(<0=0)+[yds]/[att]-3*.25+iff(>2.375=2.375),iif(<0=0)+[td]/[att]*20+iif(>2.375=2.375),iif(<0=0)+(2.375-[int]/[att]*25),iif(>2.375=2.375),iif(<0=0)*100/6)

 

[Wayne-I-M]

This will never work

=(IIf(((([comp]/[att])*100)-(30/20))<0,0,IIf(((([comp]/[att])*100)-(30/20))>2.375,2.375))+(IIf(((([yds]/[att])-3)*0.25)<0,0,IIf(((([yds]/[att])-3)*0.25)>2.375,2375)))+(IIf((([td]/[att])*20)>0,0,IIf((([td]/[att])*20)>2.375,2.375)))+(IIf((((2.375-[int])/[att])*25)>0,0,IIf((((2.375-[int])/[att])*25)>2.375,2.375))))

I would suggest breaking it down into seperate controls and then add the
result together.
The above is almost cetainly "wrong" - either not enough or too many
(((())) - as I don't understand what it is you are trying to do but hopefully
it will give you an idea as to why you should not use it and if you must
the "sort of" thing you will be looking at.

You may be better using the after update event to check results as they are
imput (or onload) and then set value as you need.


Split the whole thing down

comp/att*100-30/20

yds/att-3*.25

td/att*20

=IIf((([td]/[att])*20)>0,0,IIf((([td]/[att])*20)>2.375,2.375))


2.375-int/att*25

=IIf((((2.375-[int])/[att])*25)>0,0,IIf((((2.375-[int])/[att])*25)>2.375,2.375))


Have you tried excell - this is just what it was made for

Good luck

--
Wayne
Manchester, England.

Im running vista, microsoft office pro 2007

I'm trying to write a workable formula in an unbound text in form design.
It's a monster formula.
Their are 4 components:

comp/att*100-30/20
yds/att-3*.25
td/att*20
2.375-int/att*25

Each one of the answers for these 4 components must be checked , if greater
than 2.375 then set to 2.375 and if less than 0 then set to 0

and finally, all added together, multiplied by 100 and divided by 6

This is what I have but I get Syntax Error. I'm very new to access, so
please keep this in mind, thank you.

=PasserRating[comp]/[Att]*100-30/20+iif(>2.375=2.375),iif(<0=0)+[yds]/[att]-3*.25+iff(>2.375=2.375),iif(<0=0)+[td]/[att]*20+iif(>2.375=2.375),iif(<0=0)+(2.375-[int]/[att]*25),iif(>2.375=2.375),iif(<0=0)*100/6)

 

[Wayne-I-M]

oh I forgot about this bit

and finally, all added together, multiplied by 100 and divided by 6

Oh well - just add on to the end
####)*100)/6)
and yet another 3 ((( at the begining

Can't see this working but ......

Must admit that was quite fun

--
Wayne
Manchester, England.

Im running vista, microsoft office pro 2007

I'm trying to write a workable formula in an unbound text in form design.
It's a monster formula.
Their are 4 components:

comp/att*100-30/20
yds/att-3*.25
td/att*20
2.375-int/att*25

Each one of the answers for these 4 components must be checked , if greater
than 2.375 then set to 2.375 and if less than 0 then set to 0

and finally, all added together, multiplied by 100 and divided by 6

This is what I have but I get Syntax Error. I'm very new to access, so
please keep this in mind, thank you.

=PasserRating[comp]/[Att]*100-30/20+iif(>2.375=2.375),iif(<0=0)+[yds]/[att]-3*.25+iff(>2.375=2.375),iif(<0=0)+[td]/[att]*20+iif(>2.375=2.375),iif(<0=0)+(2.375-[int]/[att]*25),iif(>2.375=2.375),iif(<0=0)*100/6)

 

[Dennis]

Why not just CODE IT IN VBA??? You can perform the steps linearly using DIM'd
variables, in as few or as many steps as needed to do it right.

Once the prerequisite other fields are populated, just run the calculation
as a SUB.

 

[Chartswife]

I'm taking classes now for access, we just finished excel and I agree, this
formula would have worked much better in excel.
I've split the components as you suggested into seperate unbound boxes of
their own, and made a 5th box labeled "passer rating". I then refered to each
of the 4 boxes and added them, mult. by 100 and divided by 6. Unfortunately
something is still not working. I've played with the formula's a bit hopeing
to fix with no success. They are as follows:
Labeled comper:
=IIf(((([comp]/[att])*100)-(30/20))<0,0,IIf(((([comp]/[att])*100)-(30/20))>2.375,2.375))
Labeled yrdperatt:
=IIf(((([yds]/[att])-3)*0.25)<0,0,IIf(((([yds]/[att])-3)*0.25)>2.375,2375))
Labeled tdperatt:
=IIf((([td]/[att])*20)<0,0,IIf((([td]/[att])*20)>2.375,2.375))
Labeled intperatt:
=IIf((((2.375-[int])/[att])*25)<0,0,IIf((((2.375-[int])/[att])*25)>2.375,2.375))

These are each in their own unbound box.
I then made the 5th box and Labeled it passer rating and since I need to add
the answers of the above formulas together, mult. by 100 and divide by 6,
this is what I have in the control source of the 5th box:
=passerrating((([comper]+[yrdperatt]+[tdperatt]+[intperatt])*100)/6)

I will be happy when I've completed this class for sure.
If I would have thought about it sooner, I would have purchased an "Access
for Dummies" book.

Can you see where my errors are? And thank you for the assistants with this,
it is GREATLY APPRECIATED

This will never work

=(IIf(((([comp]/[att])*100)-(30/20))<0,0,IIf(((([comp]/[att])*100)-(30/20))>2.375,2.375))+(IIf(((([yds]/[att])-3)*0.25)<0,0,IIf(((([yds]/[att])-3)*0.25)>2.375,2375)))+(IIf((([td]/[att])*20)>0,0,IIf((([td]/[att])*20)>2.375,2.375)))+(IIf((((2.375-[int])/[att])*25)>0,0,IIf((((2.375-[int])/[att])*25)>2.375,2.375))))

I would suggest breaking it down into seperate controls and then add the
result together.
The above is almost cetainly "wrong" - either not enough or too many
(((())) - as I don't understand what it is you are trying to do but hopefully
it will give you an idea as to why you should not use it and if you must
the "sort of" thing you will be looking at.

You may be better using the after update event to check results as they are
imput (or onload) and then set value as you need.


Split the whole thing down

comp/att*100-30/20

yds/att-3*.25

td/att*20

=IIf((([td]/[att])*20)>0,0,IIf((([td]/[att])*20)>2.375,2.375))


2.375-int/att*25

=IIf((((2.375-[int])/[att])*25)>0,0,IIf((((2.375-[int])/[att])*25)>2.375,2.375))


Have you tried excell - this is just what it was made for

Good luck

--
Wayne
Manchester, England.

Im running vista, microsoft office pro 2007

I'm trying to write a workable formula in an unbound text in form design.
It's a monster formula.
Their are 4 components:

comp/att*100-30/20
yds/att-3*.25
td/att*20
2.375-int/att*25

Each one of the answers for these 4 components must be checked , if greater
than 2.375 then set to 2.375 and if less than 0 then set to 0

and finally, all added together, multiplied by 100 and divided by 6

This is what I have but I get Syntax Error. I'm very new to access, so
please keep this in mind, thank you.

=PasserRating[comp]/[Att]*100-30/20+iif(>2.375=2.375),iif(<0=0)+[yds]/[att]-3*.25+iff(>2.375=2.375),iif(<0=0)+[td]/[att]*20+iif(>2.375=2.375),iif(<0=0)+(2.375-[int]/[att]*25),iif(>2.375=2.375),iif(<0=0)*100/6)

 

[Chartswife]

Although I wish I were, I am not proficient in visual basics and the rest is
simply over my head at this point. But I must say that I'm finding myself
more interested in learning more than this class is teaching me. However, I
want to thank you for the response and I can't wait until I understand
exactly what it means

Why not just CODE IT IN VBA??? You can perform the steps linearly using DIM'd
variables, in as few or as many steps as needed to do it right.

Once the prerequisite other fields are populated, just run the calculation
as a SUB.

Im running vista, microsoft office pro 2007

I'm trying to write a workable formula in an unbound text in form design.
It's a monster formula.
Their are 4 components:

comp/att*100-30/20
yds/att-3*.25
td/att*20
2.375-int/att*25

Each one of the answers for these 4 components must be checked , if greater
than 2.375 then set to 2.375 and if less than 0 then set to 0

and finally, all added together, multiplied by 100 and divided by 6

This is what I have but I get Syntax Error. I'm very new to access, so
please keep this in mind, thank you.

=PasserRating[comp]/[Att]*100-30/20+iif(>2.375=2.375),iif(<0=0)+[yds]/[att]-3*.25+iff(>2.375=2.375),iif(<0=0)+[td]/[att]*20+iif(>2.375=2.375),iif(<0=0)+(2.375-[int]/[att]*25),iif(>2.375=2.375),iif(<0=0)*100/6)

 

[Wayne-I-M]

Hi

If it were me I would use some form of vba (no sure how you are getting the
info some can't help with the event you should use - are users inputting
data, are you importing this from somewhere other programme, etc)

But this said it's not too hard.

An IIf will give you true or false.
ie. IIf ([SomeThing]>1, "Yes it's more than 1", "No it's not more than 1")
In your case you are doing calculations in the IIF
IIf ([SomeThing]+[Something]>1, "Yes they are more than 1", "No they are
more than 1")
But you are also doing "sub" calculations (like in excell)
IIf ((([SomeThing]+[Something])*123)>1, "Yes the result is more than 1", "No
the result is not more than 1")
You are also you are also wanting to alter the result if the answer is
neggative (in this case if the answer is not equal to 1

IIf ((([SomeThing]+[Something])*123)>1, "Yes the result is more than 1", "No
the result is not more than 1")

IIf ((((([SomeThing]+[Something])*123)>1,"Yes the result is more than 1",
IIf ([SomeOtherCalculation]<>123, "Yes it's not more or less than 123, You
would/could enter something else here) )

Basically you need to "step through" the calculations. Do the "very" basic
stuff than add (a little at a time) the other "stuff".

I don't understand what you're trying to do so can't really help with the
specifics ie. Why are you using (#/(20/30)) why not just use /.666 ok it's
not as acurate but it will give you a result that is as near as you will get
(depending on the format).

Have a look at this link (then search google if you're still looking for
more detail)
http://msdn2.microsoft.com/en-us/library/aa445024.aspx


Good luck






--
Wayne
Manchester, England.

Although I wish I were, I am not proficient in visual basics and the rest is
simply over my head at this point. But I must say that I'm finding myself
more interested in learning more than this class is teaching me. However, I
want to thank you for the response and I can't wait until I understand
exactly what it means

Why not just CODE IT IN VBA??? You can perform the steps linearly using DIM'd
variables, in as few or as many steps as needed to do it right.

Once the prerequisite other fields are populated, just run the calculation
as a SUB.

Im running vista, microsoft office pro 2007

I'm trying to write a workable formula in an unbound text in form design.
It's a monster formula.
Their are 4 components:

comp/att*100-30/20
yds/att-3*.25
td/att*20
2.375-int/att*25

Each one of the answers for these 4 components must be checked , if greater
than 2.375 then set to 2.375 and if less than 0 then set to 0

and finally, all added together, multiplied by 100 and divided by 6

This is what I have but I get Syntax Error. I'm very new to access, so
please keep this in mind, thank you.

=PasserRating[comp]/[Att]*100-30/20+iif(>2.375=2.375),iif(<0=0)+[yds]/[att]-3*.25+iff(>2.375=2.375),iif(<0=0)+[td]/[att]*20+iif(>2.375=2.375),iif(<0=0)+(2.375-[int]/[att]*25),iif(>2.375=2.375),iif(<0=0)*100/6)

 

[Chartswife]

This was a bonus question on an old assignment that I wanted resolution for.
Basically, we pulled an excel spreadsheet that listed all the NFL
quarterbacks and their statistics, into access. Then with that information,
we had to figure each QB's individual stats per game, per attempt, and
completion percentage. The bonus question was, using the form design, add a
box titles "passer rating" and devise a formula that will work in access to
do the calculation for each QB. The formula I found on the NFL website was
the 4 components and then add all 4 answers, multiply them by 100 and divide
that by 6. Who knows where these formulas come from, lol. This file will stay
on my desktop until I figure it out. The professor uses this bonus question
every semester, so he won't give us the answer to it for fear it will make
its way to the new students I suppose. But it leaves the rest of us
wondering. No one else in the class attempted it because it was only worth an
additional 5 points, but I can't stand not knowing how it should look. Thank
you again for all the help. I've checked the link you suggested, and have
decided my next computers class will include VBA. It seems to creep up alot
in most everything we've done, and I almost feel it should have been a pre
requisite to the class I'm in now.
Thank you again.

Hi

If it were me I would use some form of vba (no sure how you are getting the
info some can't help with the event you should use - are users inputting
data, are you importing this from somewhere other programme, etc)

But this said it's not too hard.

An IIf will give you true or false.
ie. IIf ([SomeThing]>1, "Yes it's more than 1", "No it's not more than 1")
In your case you are doing calculations in the IIF
IIf ([SomeThing]+[Something]>1, "Yes they are more than 1", "No they are
more than 1")
But you are also doing "sub" calculations (like in excell)
IIf ((([SomeThing]+[Something])*123)>1, "Yes the result is more than 1", "No
the result is not more than 1")
You are also you are also wanting to alter the result if the answer is
neggative (in this case if the answer is not equal to 1

IIf ((([SomeThing]+[Something])*123)>1, "Yes the result is more than 1", "No
the result is not more than 1")

IIf ((((([SomeThing]+[Something])*123)>1,"Yes the result is more than 1",
IIf ([SomeOtherCalculation]<>123, "Yes it's not more or less than 123, You
would/could enter something else here) )

Basically you need to "step through" the calculations. Do the "very" basic
stuff than add (a little at a time) the other "stuff".

I don't understand what you're trying to do so can't really help with the
specifics ie. Why are you using (#/(20/30)) why not just use /.666 ok it's
not as acurate but it will give you a result that is as near as you will get
(depending on the format).

Have a look at this link (then search google if you're still looking for
more detail)
http://msdn2.microsoft.com/en-us/library/aa445024.aspx


Good luck






--
Wayne
Manchester, England.

Although I wish I were, I am not proficient in visual basics and the rest is
simply over my head at this point. But I must say that I'm finding myself
more interested in learning more than this class is teaching me. However, I
want to thank you for the response and I can't wait until I understand
exactly what it means

Why not just CODE IT IN VBA??? You can perform the steps linearly using DIM'd
variables, in as few or as many steps as needed to do it right.

Once the prerequisite other fields are populated, just run the calculation
as a SUB.

:

Im running vista, microsoft office pro 2007

I'm trying to write a workable formula in an unbound text in form design.
It's a monster formula.
Their are 4 components:

comp/att*100-30/20
yds/att-3*.25
td/att*20
2.375-int/att*25

Each one of the answers for these 4 components must be checked , if greater
than 2.375 then set to 2.375 and if less than 0 then set to 0

and finally, all added together, multiplied by 100 and divided by 6

This is what I have but I get Syntax Error. I'm very new to access, so
please keep this in mind, thank you.

=PasserRating[comp]/[Att]*100-30/20+iif(>2.375=2.375),iif(<0=0)+[yds]/[att]-3*.25+iff(>2.375=2.375),iif(<0=0)+[td]/[att]*20+iif(>2.375=2.375),iif(<0=0)+(2.375-[int]/[att]*25),iif(>2.375=2.375),iif(<0=0)*100/6)