Formula Help in Access

[Deb]

Hi,

I have a database for my high school students. I have it set to add up the
grades and test scores so that we can verify their credits for graduation.
My formula is set up so that if a student gets a State test score of 400 or
greater and a letter grade of D- or better they get a verified credit for
graduation. If they recieve a State score of 399 or below and a letter grade
of E or below, they do not receive the credit. We also have a couple of other
tests they can take to receive credit for a standard diploma. Then there are
some special programs at our school that allow the student to "test" out of a
particular subject. When they do, we assign them a "P" for passing instead
of the traditional letter grade. Since this needs to go into the letter
grade of my formula, it will not pick it up as a "passing" grade. Since "P"
is after "E" in the alphabet, it will not assign a credit. I tried putting
"P" after the <"E", but it did not work. Any help with this one would be
most appreciated...

=IIf([ALGI]>"399""S" And [AIGR]<"E",1,0)+IIf([GEOMETRY]>"399""S" And
[GEOGR]<"E",1,0)+IIf([ALGII]>"399""S" And [AIIGR]<"E",1,0)

 

[Lord Kelvan]

ok so if i get you you are saying if they get a p regardless of the
number they pass so if they have 399 and a p they still pass

i dont understand your fields what data do eachof them contain

ALGI
AIGR
GEOMETRY
GEOGR
ALGII
AIIGR

where is the P stored and the letter grades and the numeric value and
what does each field mean

that and what are you trying to achieve with

[ALGI]>"399""S"

i could just be reading it wrong but it dosnt seem to make sence to me

ill be able to help you if you can give me that info

Regards
Kelvan

 

[John Spencer]

That looks as if it is complex enough that you might consider writing a
VBA function.

Also, are scores text or are they numbers. This could be crucial as 99
as text is greater than 399 as text.

Your expression does not look at all correct to me.

So are the rules as follows:

IF Algi >=400 OR Aigr <> "E" then credit granted
If Geometry >= 400 or GeoGr <> "E" then credit granted
If Algii >= 400 or Aiigr <> "E" then credit granted


What is the meaning of the "S" after the "399"?

Are there other test/grade subject combinations?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Deb]

Sorry for the confusion. Keep in mind I did not name these fields. I work
with educators who have been doing this work for a VERY long time (with
Excel). I found out the hard way you cannot just come in and change things
around.

ALGI = the State's test score (number)
AIGR = the grade from the teacher (letter)
GEOMETRY = the State's test score (number)
GEOGR = the grade from the teacher (letter)
ALGII = the State's test score (number)
AIIGR = the grade from the teacher (letter)

Using only Algebra I (the first two columns - ALGI and AIGR) this is what
happens...

John takes an Algebra I class this year and takes the State's mandated test
for the subject. He gets a score of "399" (entered into ALGI), however, he
gets a grade of "B" (entered into AIGR) from the teacher. He would NOT
receive a credit - "399" is not a "passing" score, eventhough "B" is a
"passing" grade - they both need to be "passing" in order for credit to be
granted. He gets a chance to take the Algebra I test again about 4 times
before graduation, but he just cannot get past that "399" (this has
happened). A meeting is held with the School Board and they decide to go
ahead and give him the credit so he can advance with his class. Instead of a
number he receives a "S" in the ALGI (State's score) column. So now he has a
"S" (entered in ALGI) in his score and a letter grade of "B" (entered in
AIGR). He now receives 1 math credit for Algebra I.

=IIf([ALGI]>"399""S" And [AIGR]<"E",1,0) - If "ALGI" is greater than "399"
or "S" AND "AIGR" is better than the letter "E" you get "1" credit -
otherwise you get "0" credits.

I have this formula set up for all of my grades that are required for credit
and graduation. Then for each area of learning (Math, History, English, and
Science) they all add together (i.e. John passed both the State's test (or
was credited by the school board) and the class in all of his math classes by
12th grade so Algebra I, Algebra II, Geometry added together would give him 3
verifieid credits) and that number gets calculated between the three separate
math classes and put into the "MATH" field to show how many total credits he
has to graduate with in "MATH".

The problem I am having is I cannot get it to do it in the "AIGR" for the
letter grade. They just accepted a student who came from a private school
and they let her test out of Algebra I. They gave her a "P" for passing.
They said they cannot post a letter grade since she did not earn it while
with our school district....rules! In any case I tried to put the "P" in
just like the "S" that worked on the other side, but it totally ignores it.
It shows up in the field when I type it, however, it does not add to give me
the credit. She still has a passing State test score. They are REQUIRED to
take these tests no matter how they are schooled unless exempted by the
State. So technically she should have a "420" in "ALGI" (the State's test
score) and a "P" in AIGR (our letter grade for her) and the two should add up
to 1 credit. I tried writing it like this.....

=IIf([ALGI]>"399""S" And [AIGR]<"E""P",1,0)

just like it is on the other side, however, it would not work. I even tried
to move the "P" in front of the <"E" and when I did that it gave me an #Name
error in my "MATH" field where it adds my total credits for this subject.

I hope this was not too long and explained better how it works. It really
does work great for what we use it for. If you need more info or I have
confused you please let me know. I so appreciate any help with this.

=IIf([ALGI]>"399""S" And [AIGR]<"E",1,0)+IIf([GEOMETRY]>"399""S" And
[GEOGR]<"E",1,0)+IIf([ALGII]>"399""S" And [AIIGR]<"E",1,0)

--
Deb

ok so if i get you you are saying if they get a p regardless of the
number they pass so if they have 399 and a p they still pass

i dont understand your fields what data do eachof them contain

ALGI
AIGR
GEOMETRY
GEOGR
ALGII
AIIGR

where is the P stored and the letter grades and the numeric value and
what does each field mean

that and what are you trying to achieve with

[ALGI]>"399""S"

i could just be reading it wrong but it dosnt seem to make sence to me

ill be able to help you if you can give me that info

Regards
Kelvan

 

[John Spencer]

Simple little function

Public Function fCredit(TestScore, Grade) as Integer
Dim iReturn as Integer
iReturn = 1 'Assume success

IF Len(TestScore & "") = 0 OR Len(Grade & "") = 0 Then
'Missing or blank values
iReturn = 0
ELSE
If Val(TestScore)<399 AND Instr(1,"S",TestScore,1)=0 Then
'Insufficient Test score
iReturn = 0
ElseIf UCase(Grade) Like "*E*"
'If the grade
iReturn = 0
END IF

fCredit = iReturn
End Function

Copy and paste that into a VBA module and save the module. The name of the
module must be something other than fCredit.

Now you can call that function in your query or in a form or in a report.

=fCredit([ALGI],[AIGR]) + fCredit([GEOMETRY],[GEOGR]) + fCredit([ALGII],[AIIGR])

And if the criteria change, you modify the function and everywhere it is used
it will automatically work in the new way. For instance, if someone decides
that a passing test score can also be "P" (for passed) then you just change
the line to
If Val(TestScore)<399 AND Instr(1,"PS",TestScore,1)=0 Then


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Sorry for the confusion. Keep in mind I did not name these fields. I work
with educators who have been doing this work for a VERY long time (with
Excel). I found out the hard way you cannot just come in and change things
around.

ALGI = the State's test score (number)
AIGR = the grade from the teacher (letter)
GEOMETRY = the State's test score (number)
GEOGR = the grade from the teacher (letter)
ALGII = the State's test score (number)
AIIGR = the grade from the teacher (letter)

Using only Algebra I (the first two columns - ALGI and AIGR) this is what
happens...

John takes an Algebra I class this year and takes the State's mandated test
for the subject. He gets a score of "399" (entered into ALGI), however, he
gets a grade of "B" (entered into AIGR) from the teacher. He would NOT
receive a credit - "399" is not a "passing" score, eventhough "B" is a
"passing" grade - they both need to be "passing" in order for credit to be
granted. He gets a chance to take the Algebra I test again about 4 times
before graduation, but he just cannot get past that "399" (this has
happened). A meeting is held with the School Board and they decide to go
ahead and give him the credit so he can advance with his class. Instead of a
number he receives a "S" in the ALGI (State's score) column. So now he has a
"S" (entered in ALGI) in his score and a letter grade of "B" (entered in
AIGR). He now receives 1 math credit for Algebra I.

=IIf([ALGI]>"399""S" And [AIGR]<"E",1,0) - If "ALGI" is greater than "399"
or "S" AND "AIGR" is better than the letter "E" you get "1" credit -
otherwise you get "0" credits.

I have this formula set up for all of my grades that are required for credit
and graduation. Then for each area of learning (Math, History, English, and
Science) they all add together (i.e. John passed both the State's test (or
was credited by the school board) and the class in all of his math classes by
12th grade so Algebra I, Algebra II, Geometry added together would give him 3
verifieid credits) and that number gets calculated between the three separate
math classes and put into the "MATH" field to show how many total credits he
has to graduate with in "MATH".

The problem I am having is I cannot get it to do it in the "AIGR" for the
letter grade. They just accepted a student who came from a private school
and they let her test out of Algebra I. They gave her a "P" for passing.
They said they cannot post a letter grade since she did not earn it while
with our school district....rules! In any case I tried to put the "P" in
just like the "S" that worked on the other side, but it totally ignores it.
It shows up in the field when I type it, however, it does not add to give me
the credit. She still has a passing State test score. They are REQUIRED to
take these tests no matter how they are schooled unless exempted by the
State. So technically she should have a "420" in "ALGI" (the State's test
score) and a "P" in AIGR (our letter grade for her) and the two should add up
to 1 credit. I tried writing it like this.....

=IIf([ALGI]>"399""S" And [AIGR]<"E""P",1,0)

just like it is on the other side, however, it would not work. I even tried
to move the "P" in front of the <"E" and when I did that it gave me an #Name
error in my "MATH" field where it adds my total credits for this subject.

I hope this was not too long and explained better how it works. It really
does work great for what we use it for. If you need more info or I have
confused you please let me know. I so appreciate any help with this.

=IIf([ALGI]>"399""S" And [AIGR]<"E",1,0)+IIf([GEOMETRY]>"399""S" And
[GEOGR]<"E",1,0)+IIf([ALGII]>"399""S" And [AIIGR]<"E",1,0)

 

[John Spencer]

DANG NAB IT! The function had some syntax errors. I should have checked.
Hopefully this one is correct

Public Function fCredit(TestScore, Grade) As Integer
Dim iReturn As Integer
iReturn = 1 'Assume success

If Len(TestScore & "") = 0 Or Len(Grade & "") = 0 Then
'Missing or blank values
iReturn = 0
Else
If Val(TestScore) < 399 And InStr(1, "S", TestScore, 1) = 0 Then
'Insufficient Test score
iReturn = 0
ElseIf UCase(Grade) Like "*E*" Then
'If the grade
iReturn = 0
End If
End If

fCredit = iReturn
End Function

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Deb]

Hi John Spencer,

I cannot thank you enough! This worked like a charm. Sorry it took so long
to get back, I have been extremely busy trying to get this out to the High
Schools.