Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Toledo, Ohio, USA
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    #Error or #Name in calculated control

    <P ID="edit"><FONT SIZE=-1>Edited by jp2558 on 01/03/27 13:33.</FONT></P>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.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: #Error or #Name in calculated control

    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.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Toledo, Ohio, USA
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Error or #Name in calculated control

    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.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Error or #Name in calculated control

    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.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Toledo, Ohio, USA
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Error or #Name in calculated control

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •