R
Richard Hollenbeck
Once I get this working I'll need to use this code several times in one form
or report.
The following code typed into a text box returns the "#Name?" error:
=Nz(DLookup("groupWeight","groups","groups.courseCode= '"
& [courseCode] & "'"),1) /
Nz(DSum("groupWeight","groups","groups.courseCode= '"
& [courseCode] & "'"),1) *
Nz(DLookup("score","studentScores","activities.activityID= "
& [studentScores].[activityID] & "AND [studentScores].[studentID]= "
& [students].[studentID]),1) *
Nz(DLookup("activityWeight","activities","activities.groupID= "
& [groups].[groupID]),1) /
Nz(DSum("activityWeight","activities","activities.groupID= "
& [groups].[groupID]),1)
Could the problem be that I'm trying to have two criteria in the score
lookup? Maybe I need to reference the query instead of the actual tables
the query is based on? Maybe just list the query's fields instead of even
using a lookup at all?
I'm wondering if I have an "order of operations" problem here. I suppose I
could add parentheses to order the divisions to calculate first, but I don't
believe that would cause the "#Name?" problem. I think it would simply give
me the wrong answer.
I made the Nz() function default to 1 in an effort to avoid potential divide
by zero errors.
The form's record source is a query that provides all these tables and
fields.
courseCode is a text value, while groupID, activityID and studentID are all
long integers.
This is approximately the code I will need to get each student's semester
current average--I think. Once I get this working correctly, I'll need to
add a count to get all students with a semester average of >=92 for the A
box, >=82 for the B box, >=72 for the C box, etc. However, I'll never get
to do that if I don't even get this working.
In some kind of pseudocode, the formula should work something like this:
count of: ((this activity's weight / sum of all activities' weights in this
group) * (this group's weight / sum of all groups' weights in this course) *
(this student's score in this activity)) where result >=92, etc.
Thanks.
Rich Hollenbeck
or report.
The following code typed into a text box returns the "#Name?" error:
=Nz(DLookup("groupWeight","groups","groups.courseCode= '"
& [courseCode] & "'"),1) /
Nz(DSum("groupWeight","groups","groups.courseCode= '"
& [courseCode] & "'"),1) *
Nz(DLookup("score","studentScores","activities.activityID= "
& [studentScores].[activityID] & "AND [studentScores].[studentID]= "
& [students].[studentID]),1) *
Nz(DLookup("activityWeight","activities","activities.groupID= "
& [groups].[groupID]),1) /
Nz(DSum("activityWeight","activities","activities.groupID= "
& [groups].[groupID]),1)
Could the problem be that I'm trying to have two criteria in the score
lookup? Maybe I need to reference the query instead of the actual tables
the query is based on? Maybe just list the query's fields instead of even
using a lookup at all?
I'm wondering if I have an "order of operations" problem here. I suppose I
could add parentheses to order the divisions to calculate first, but I don't
believe that would cause the "#Name?" problem. I think it would simply give
me the wrong answer.
I made the Nz() function default to 1 in an effort to avoid potential divide
by zero errors.
The form's record source is a query that provides all these tables and
fields.
courseCode is a text value, while groupID, activityID and studentID are all
long integers.
This is approximately the code I will need to get each student's semester
current average--I think. Once I get this working correctly, I'll need to
add a count to get all students with a semester average of >=92 for the A
box, >=82 for the B box, >=72 for the C box, etc. However, I'll never get
to do that if I don't even get this working.
In some kind of pseudocode, the formula should work something like this:
count of: ((this activity's weight / sum of all activities' weights in this
group) * (this group's weight / sum of all groups' weights in this course) *
(this student's score in this activity)) where result >=92, etc.
Thanks.
Rich Hollenbeck