problem in form with field whose value calcualted in query

[viwaltzer]

I have a form, frmTests, that updates table A. I need to display a field that is a calculation of 4 fields in table A with them being multiplied by values in table B. I wrote a query that calculates this field. It will work correctly if I select 1 record from table A. I think I have the where clause to select the key from the form. Here's my query which I'll call qry1:

SELECT B.Factor * (A.fld1 ^ B.fld1Power) * (A.fld2 ^ B.Fld2Power) as answer
FROM A,B
where A.PID=[Forms]![frmTests]![PID]
and B.AsOfDate = (select max(AsOfDate) from B)

It works just like I want it to if I substitute a valid PID for "[Forms]![frmTests]![PID]".

In my form, the text box field, named boxCalc, has control source as "=[qry1]![answer]". The key PID is in a text box named PID. When I run the form, boxCalc has "#Name?" for all records.

I am new to Access & would appreciate any help. Thanks much.:banghead:

 

[boblarson]

You can't refer to a query like that in a control source. You would need to use a DLOOKUP instead.

=Nz(DLookup("[answer]", "qry1", "[PID]=" & [PID]),"")