Results 1 to 7 of 7

Thread: Query

  1. #1

    Thread Starter
    Member
    Join Date
    Nov 1999
    Location
    Dover, NH
    Posts
    54

    Post

    Have you printed the statement out, say using Debug.Print, with the values from the combobox before it is executed? What does it look like then? It looks a little strange here. I'd like to see the values first.

  2. #2
    Serge's Avatar
    Join Date
    Feb 1999
    Location
    Scottsdale, Arizona, USA
    Posts
    2,744

    Post

    If I understand you correctly, you want to show everything from tables Names and Act where name equals to Combobox.Text.

    Code:
    sSQL = "select Act.*, Names.* from Act, Names where (Names.name = Act.name) And (Act.name ='" & Trim(Combo1.Text) & "')"
    myRecordset13.Open sSQL, MyConnection, adOpenStatic
    Assuming that you use ADO.

    ------------------

    Serge

    Software Developer
    Serge_Dymkov@vertexinc.com
    Access8484@aol.com
    ICQ#: 51055819



    [This message has been edited by Serge (edited 12-01-1999).]

  3. #3
    Addicted Member
    Join Date
    Nov 1999
    Posts
    153

    Post

    My PC crashed without me saving the changes to the code. By restructing that line of code I was able to get it to work. As it turns out I was leaving the ' out of my select statement. This is code is a little different than the one I presented online, but I was getting the same error message.

    sSQL = "select * from names where names = '" & Trim(Combo1.Text) & "'"

    Thanks!

  4. #4
    Serge's Avatar
    Join Date
    Feb 1999
    Location
    Scottsdale, Arizona, USA
    Posts
    2,744

    Post

    As I understand, Names is a table name, so your statement:

    sSQL = "select * from names where names = '" & Trim(Combo1.Text) & "'"

    Notice that I emphasized the "names". it means, it expects the syntax: <TableName>.<FieldName>, so I would assume that you want to get, let's say, FirstName from the Names table:

    sSQL = "Select * From Names Where Names.FirstName = '" & Trim(Combo1.Text) & "'"

    If I missunderstood something, please show us the table structure (i.e. fields in that table)

    ------------------

    Serge

    Software Developer
    Serge_Dymkov@vertexinc.com
    Access8484@aol.com
    ICQ#: 51055819


  5. #5
    Addicted Member
    Join Date
    Nov 1999
    Posts
    153

    Post

    The table Names has the same names as the table Act. However, the table Names has a more detail description that I want to use instead the field names. The field I want to use from the Names table is called Description. The combo1 box is filled with names from ACT table which should be the same names in the Names table.

    Here's the code that pertains to this section of the program.

    sSQL = "select * from names where names = '" & Trim(Combo1.Text) & "'"
    Set myRecordset13 = myConnection.Execute(sSQL)
    List13.AddItem IIf(IsNull(myRecordset13!Description), "0", myRecordset13!Description)

    I tried putting the results in a label, but it go to complicated.

  6. #6
    Addicted Member
    Join Date
    Nov 1999
    Posts
    153

    Post

    What's wrong with this code? I get a syntax error during run-time. What I'm trying do is join two tables in the select statement. Currently, combo1 is filled with the names from ACT, and I want to use the name in combo1 to get the description from the Names table.

    sSQL = "select * from act, Names where Names.names =act.name ='" & Trim(Combo1.Text) & ""
    Set myRecordset13 = myConnection.Execute(sSQL)

  7. #7

    Thread Starter
    Member
    Join Date
    Nov 1999
    Location
    Dover, NH
    Posts
    54

    Post

    OK, I'm confused now :-) Is it working now or are you getting the same error?

    Did you print out the statement before it was used? The debug.print statement is very helpful. It will show up in the immeadiate window. The problem is very likely pretty simple, seeing the sql before it gets sent to the object is probably your best bet. But then again, I may be barking up the wrong tree here.

Posting Permissions

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



Click Here to Expand Forum to Full Width