Displaying result from a query in a form results in "#Name? being returned

[Jamescdawson]

Hello All,



I've built a Query that is intended to sum the payments made by individual clients. It looks at all payments made to each account and returns the total for that client. The query works fine and it sums all the payments made by that customer against that customer's name.

There is a 1 to Many relationship between the Client Table and the Payments table.

I then included a Text box field in the "Client Record form" where I want to display this calculated sum. (This is so that the DB user can see what's been paid to account so far for each customer simply by looking at that client's Record. I intend to have a second box showing "Amount Outstanding" which will be a calculation.

In the control source field for the text box, I put

"=[QryTotalPaymentsReceived].[SumOfPaymentReceived]" This is the field where the total payments is returned in the query

Dispite the query having references to the client & clientID, the sum figure is not displayed. Instead, I get "#Name?"

Can anyone tell me what am I missing? or where I'm going wrong

Jimbo

[John_G]

What is the recordsource for the form? If it is not QryTotalPaymentsReceived then that is the problem. If you want to retrieve the value of SumOfPaymentReceived from the query, you probably sould be using DLookup, something like this:

=Dlookup("SumOfPaymentReceived","QryTotalPaymentsReceived","Criteria...")

Where "Criteria" identifies the current customer (that is only a guess, based on your description), and presumably the current customer number is in a form field.

[Jamescdawson]

Hi John G, Thanks for the (very) quick response.

Schoolboy error... The record source for the form is the Client & Customer Table.

I tried DLookup like this....

=DLookUp("SumOfPaymentReceived","QryTotalPaymentsR eceived","Surname")

and

=DLookUp("SumOfPaymentReceived","QryTotalPaymentsR eceived","CustomerID")

But it returns the first record in the query irrespective of which client record I go to... Do I need to give more detail in the criteria ?

[Jamescdawson]

John G

GOT IT

=DLookUp("SumOfPaymentReceived","QryTotalPaymentsR eceived","[CustomerID] = Form![CustomerID]")

Thanks for your help ! Much appreciated