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

[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

 

[Guest]

What's the name of your text box? If it's named "groups", "groupWeight", or
any of the columns/tables listed in your DLookup, that could be the problem.

I used to have that #Name? error all the time, and it came from when I used
the report wizards, which name text boxes the same as the fields they're
bound to.

That'd be the first thing I'd check.

 

[Richard Hollenbeck]

Thank you for your rapid reply!

The text box is called "txtA" because I will eventually want to make it
query a count of all A's in the class. I have other text boxes called,
"txtB", "txtC", "txtD", and "txtF". The form (maybe I'll do it as a report)
will display a breakdown of the class statistics, like how many A, B, C, D,
and F grades are in a course. I purposely make sure all my text boxes have
names different enough from the field names to avoid confusion. It's
ridiculous that Access automatically defaults the text boxes names as the
name of the field, since Microsoft ought to know it causes problems. I
can't figure out why they did that. I also noticed that by default, Access
makes queries, forms, and reports have the same name as the record source.
Dumb! Why doesn't it use naming conventions like the world of Access users
use? Just rhetorical questions.

Anyway, back to the topic at hand; no, my text boxes don't have the same
name as the field names.

Rich

What's the name of your text box? If it's named "groups", "groupWeight", or
any of the columns/tables listed in your DLookup, that could be the problem.

I used to have that #Name? error all the time, and it came from when I used
the report wizards, which name text boxes the same as the fields they're
bound to.

That'd be the first thing I'd check.

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

 

[Guest]

In that case, maybe you can break that huge function into several smaller
parts, and store some of the data in invisibe text boxes. For instance, make
a text box called "txtScoreForThisActivity" and put this in it:

Nz(DLookup("score","studentScores","activities.activityID= "
& [studentScores].[activityID] & "AND [studentScores].[studentID]= "
& [students].[studentID]),1)

Then make a text box called "txtActivityWeightForGroup" and put this in it:
Nz(DLookup("activityWeight","activities","activities.groupID= " &
[groups].[groupID]),1)

etc. In fact, I'd suggest leaving them visible at first, to make sure
they're returning the right values. Then try something like this:

(([txtThisGroupWeight] / [txtGroupActivityWeightSum]) *
([txtThisActivityWeight]/ [txtActivityWeightSum]) *
([txtScoreForThisActivity]))

etc. I'm presuming a lot about text box names, and so forth.

In the nutshell, I'm saying break apart the pieces of that monster
calculation. A) It's easier to work with, and B) you'd better be able to see
where the problem lies.

Thank you for your rapid reply!

The text box is called "txtA" because I will eventually want to make it
query a count of all A's in the class. I have other text boxes called,
"txtB", "txtC", "txtD", and "txtF". The form (maybe I'll do it as a report)
will display a breakdown of the class statistics, like how many A, B, C, D,
and F grades are in a course. I purposely make sure all my text boxes have
names different enough from the field names to avoid confusion. It's
ridiculous that Access automatically defaults the text boxes names as the
name of the field, since Microsoft ought to know it causes problems. I
can't figure out why they did that. I also noticed that by default, Access
makes queries, forms, and reports have the same name as the record source.
Dumb! Why doesn't it use naming conventions like the world of Access users
use? Just rhetorical questions.

Anyway, back to the topic at hand; no, my text boxes don't have the same
name as the field names.

Rich

What's the name of your text box? If it's named "groups", "groupWeight", or
any of the columns/tables listed in your DLookup, that could be the problem.

I used to have that #Name? error all the time, and it came from when I used
the report wizards, which name text boxes the same as the fields they're
bound to.

That'd be the first thing I'd check.

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

 

[Richard Hollenbeck]

Great idea. It will also make it easier to troubleshoot later (long after
the code becomes "cold.") Thanks for the idea. I'll try it and get back to
the group.

Rich

In that case, maybe you can break that huge function into several smaller
parts, and store some of the data in invisibe text boxes. For instance, make
a text box called "txtScoreForThisActivity" and put this in it:

Nz(DLookup("score","studentScores","activities.activityID= "
& [studentScores].[activityID] & "AND [studentScores].[studentID]= "
& [students].[studentID]),1)

Then make a text box called "txtActivityWeightForGroup" and put this in it:
Nz(DLookup("activityWeight","activities","activities.groupID= " &
[groups].[groupID]),1)

etc. In fact, I'd suggest leaving them visible at first, to make sure
they're returning the right values. Then try something like this:

(([txtThisGroupWeight] / [txtGroupActivityWeightSum]) *
([txtThisActivityWeight]/ [txtActivityWeightSum]) *
([txtScoreForThisActivity]))

etc. I'm presuming a lot about text box names, and so forth.

In the nutshell, I'm saying break apart the pieces of that monster
calculation. A) It's easier to work with, and B) you'd better be able to see
where the problem lies.

Thank you for your rapid reply!

The text box is called "txtA" because I will eventually want to make it
query a count of all A's in the class. I have other text boxes called,
"txtB", "txtC", "txtD", and "txtF". The form (maybe I'll do it as a report)
will display a breakdown of the class statistics, like how many A, B, C, D,
and F grades are in a course. I purposely make sure all my text boxes have
names different enough from the field names to avoid confusion. It's
ridiculous that Access automatically defaults the text boxes names as the
name of the field, since Microsoft ought to know it causes problems. I
can't figure out why they did that. I also noticed that by default, Access
makes queries, forms, and reports have the same name as the record source.
Dumb! Why doesn't it use naming conventions like the world of Access users
use? Just rhetorical questions.

Anyway, back to the topic at hand; no, my text boxes don't have the same
name as the field names.

Rich

What's the name of your text box? If it's named "groups",

"groupWeight",
or

any of the columns/tables listed in your DLookup, that could be the problem.

I used to have that #Name? error all the time, and it came from when I used
the report wizards, which name text boxes the same as the fields they're
bound to.

That'd be the first thing I'd check.

:

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