Results 1 to 6 of 6
  1. #1
    EvanScamman
    Guest

    SQL statement in the ControlSource of a text box (Access)

    I would like to set the control source property of a text box to this query:

    SELECT Sum(tblOrderRequest.Cost) AS SumOfCost, tblOrderRequest.OrderRequestLinkID FROM tblOrderRequest GROUP BY tblOrderRequest.OrderRequestLinkID HAVING (((tblOrderRequest.OrderRequestLinkID)=[Forms]![frmOrderRequest]![OrderRequestLinkID]));

    I tried typing an equals sign and then the query, but that doesn't. Is it possible to do this? is there a better way? Thank you,
    Evan

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL statement in the ControlSource of a text box (Access)

    Your best bet here is to use the builder - but it's rather unusual to use a SQL Select statement as the data source for a text box (you often do it for combo boxes however). In general, you make the expression or whatever a part of the query that the form is bound to. (I'm assuming that you are using a bound form, not an unbound one.)

    Also note that the HAVING statement is seldom used in Access - the WHERE clause is usually preferred.
    Wendell

  3. #3
    New Lounger
    Join Date
    May 2002
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL statement in the ControlSource of a text box (Access)

    One problem with your SQL statement is that you are returning two fields to the text box. That likely makes it not work. If you are using the query builder to create the SQL statement, then uncheck the "Show" box for the criteria field.

  4. #4
    EvanScamman
    Guest

    Re: SQL statement in the ControlSource of a text box (Access)

    actually, my form is not bound to a table. that's why i need to get my data from a SQL statement. is this possible? can I run an SQL statement (sum) in VB and then set the text box to equal the single number result? it seems like this could be done, but i don't know how to do it.

  5. #5
    EvanScamman
    Guest

    Re: SQL statement in the ControlSource of a text box (Access)

    I unchecked the check box, then i select the SQL statement, copy it, and paste into the ControlSource in the text box properties. Do i need to put an = first then the SQL? should i put it in quotes too?
    I seem to get an error any way i try it.
    It makes me wonder if it is even possible to do this...
    Evan

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

    Re: SQL statement in the ControlSource of a text box (Access)

    Two problems. One is that your SQL returns more than one field. The other is that you can't do it this way. Use a DSum doman aggregate function instead as the data source for your textbox. You can use SQL statements to populate comboboxes and listboxes, but not other controls.
    Charlotte

Posting Permissions

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