#Error or #Name in calculated control

Topic

New Reply

Edited by jp2558 on 01/03/27 13:33.

Access97 on WIN98se

I have two controls for which I’m trying to perform a subract operation to then show the result in a third control. The first control is a text box that is bound to an interger in a table. (Its value represents the number of seats an event will handle at capacity.) The second control is a list box that returns one field from one record via a query. (This is the number of seats for which people have already reserved for the event.) When I try to subtract the contents of the second field from the first and show it in a third control (text box) I get #Error displayed in the control. (This result field is supposed to tell me how many seat I have available until the event is sold out.) If I change the control to a list box and perform the subtraction I simply get an empty list box.

First question: Why do a text box and a list box react differently? I had originally intended to use a text box to display the query result but only received the #NAME/#ERROR when it was defined as a text box, and after changing it to a list box, the result of the query is properly displayed.

Second question (more obvious): How do I perform the subtraction of these two controls and display the result on a form?

Thanks in advance.

Reply | Quote

Replies

You’ll get more useful help if you show us what you actually did. What formula did you try to use in your third control? Did you give the controls unique names to distinguish them from the underlying fields? How many columns are there in the listbox? I assume the query behind the listbox is not the same as the query behind the form. How many rows are returned by the query behind the listbox? Is the listbox bound to a field? If not, how are you determining its value?

I don’t understand your reference to displaying the query result in a textbox. You cannot bind a textbox to a query, although you can bind it to a field in a query underlying the form.

Reply | Quote

For the third control I’m using the statement builder to explicitly point to the first control on the form. Then I use a minus sign and point to the (only) field in the query. The controls have unique names to define them. The following is the control source for the third control:

=[EventCapacity]-[SumQtySeats]![SumOfqtyseats]

[EventCapacity] is the first field and is the interger that is bound to the underlying table for the form.

[SumQtySeats]![SumOfqtyseats] is the query name and SumOfqtyseats is the name of the only field in the query.

The list box contains only one column and your assumption is true regarding the query behind the list box is not the same source for the other fields on the form. The listbox is bound to the field in the query. I can display in separate fields on the form both of the controls for the first and second fields.

Reply | Quote

You could try removing the name of the query from your expression.
Or create an edit (hidden?) on the form and use Dlookup to get the Sumofqtyseats to fill that control. Then use this field in your calculation.

Reply | Quote

Andy – thanks so much. Your suggestion to use DLOOKUP worked.

Reply | Quote