What causes the "#Name?" error in text box?

From: Richard Hollenbeck (richard.hollenbeck_at_verizon.net)
Date: 09/29/04 

Date: Wed, 29 Sep 2004 14:30:31 GMT

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

Relevant Pages

  • Re: WEIGHT confusion (SQL 2k, running on Win2k3)
    ... what I think you really need is a multiple column join using a single ... multiple CONTAINSTABLE query will perform very poorly as each CONTAINSTABLE ... >> is all basic configuration info necessary to troubleshoot SQL FTS ... >> but I need to test it out a bit with a variable weight parameter. ...
    (microsoft.public.sqlserver.fulltext)
  • saved queries based on other saved queries vs. one big query in a forms module using DAO
    ... flawed set of queries I made and now it's finally back to the drawing board. ... I have several saved queries, ... The activities each have a weight within that group and the ... The first time I tried this I made a query that got the summary infomation ...
    (microsoft.public.access.queries)
  • RE: subtraction
    ... completed all changes you suggested and the Query works until I try nto make ... a report with it or a form in datasheet view. ... Is there anyway to do this on the actual form where I enter the weight info? ... >>> In the report instead of having a running sum is there a way to have a ...
    (microsoft.public.access.reports)
  • Re: How can I create a list query?
    ... "Lost in Query" wrote: ... weight gain or loss compared to each other. ... over on a Older table's NameID field. ... Newer table down in to field row ...
    (microsoft.public.access.queries)
  • Re: why does MATCH/AGAINST fail to catch entries that LIKE does catch?
    ... "The MySQL FULLTEXT implementation regards any sequence of true word characters as a word. ... Apostrophes at the beginning or the end of a word are stripped by the FULLTEXT parser; 'aaa'bbb' would be parsed as aaa'bbb. ... "Every correct word in the collection and in the query is weighted according to its significance in the collection or query. ... Consequently, a word that is present in many documents has a lower weight, because it has lower semantic value in this particular collection. ...
    (comp.lang.php)