[access 2000] formula yielding #name

[ed_the_unlucky]

situation: form displays a value, derived from the control source (a table). this is a value which can be changed by the presence of one or more associated records in a different table.

problem: the different table in question has 0 att'd records.

now, it's easy enough to create a query to find any such associated records in the other table, but since there are no records, i will now and perhaps for a while (when this goes live) still receive "". b/c this is the case, the calculation yields the #name value.

what i want is to generate some form of text using the iif function, but in the future, when records are added, want to be able to perform the calculation w/out having to go into the form and play with the data source.

is there something simple that i'm missing here? the calculation in question:

=IIf(qryCurrentStoredWtIsNull3!NoStoredWt="None","None",qryCurrentStoredWt2!Weight_Out)

arguments:
qryCurrentStoredWtIsNull3!NoStoredWt is the result of qrys 1 and 2 preceding it, finding non-matched PKs b/n the two tables. the intention here is to test to see if the aforementioned associated records are or aren't present: the named query directly above produces "none" on a non-match.

the second "none" is the value i want this field to take when no such records exist.

qryCurrentStoredWt2!Weight_Out is the result of a preceding query that collects the aforementioned associated records, taking the sum of the values of those records (all stored as numbers).

am i just being too greedy in wanting to accomplish this with the same field?

any insights would be appreciated.

ed

 

[ed_the_unlucky]

OK, i see that what i'm doing here isn't working b/c the query is passing a result of 0 records, and i'm doing a sum on that field.

but i still need to try to produce this figure, the result of a static weight and which may have 0-3 records reducing the weight. and i'm at an utter loss re: how to do so.

anybody have any thoughts?

ed