Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    117

    Unanswered: Query with MS Access 2007 using VB6.0

    Hello sir,
    I used to below coding to view the records from my database from VB6.0 command click.

    db.Execute "select &list1.name&, & list1.no & , & list3.no1 & , & list3.no2 & , & list2.name & , & list2.no & from & list1 & , & list3 & , & list2 & where & list1.no & = & list3.no1 & and & list1.name & = & list2.name & and & list2.no & < & list3.no2 &"

    But the above coding is not working. Pls do anyone help me in this coding sir.

    All message the procedure for running ms access query from VB.

    Thank you sir.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try removing the ampersands.. they would only be needed if you build the sql using VBA

    Code:
    db.Execute "select list1.name, list1.no, list3.no1, list3.no2, list2.name, list2.no from list1, list3, list2 where list1.no = list3.no1 and list1.name  = list2.name and list2.no < list3.no2"
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2008
    Posts
    117
    Quote Originally Posted by healdem
    try removing the ampersands.. they would only be needed if you build the sql using VBA

    Code:
    db.Execute "select list1.name, list1.no, list3.no1, list3.no2, list2.name, list2.no from list1, list3, list2 where list1.no = list3.no1 and list1.name  = list2.name and list2.no < list3.no2"
    After executing this query i made it to added in listboxes. but it actually adding the query itself but not the data.
    Pls help in this

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I suggest you have a closer look at using strings and concatenation before going any further. Not sure if the SQL is clean or not either.

    I would expect it to be something more like this:

    db.Execute "SELECT " & list1.name & ", " & list1.no & ", " & list3.no1 & ", " & list3.no2 & ", " & list2.name & ", " & list2.no & " FROM " & list1 & ", " & list3 & ", " & list2 & " WHERE " & list1.no & "=" & list3.no1 & " and " & list1.name & "=" & list2.name & " AND " & list2.no & "<" & list3.no2
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im confused
    are list1, list2 & list3 tables or list boxes

    you would use the construct in post #2 if you are selecting columns from a table
    you would use the construct in post#4 if you are building the SQL based on values elsewhere say a list or combo box.

    perhaps you cna explain what it is you are trying to do

    are you trying to populate a list box
    are you trying to retrieve columns form a table based on user selection

    what are the columns/fields you want to extract from the table.

    I don't think the SQL makes sense
    Im guessing you are trying to retrieve some values from a table(s), but its not clear (to me)
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Create a string variable, assign the string you want to execute to that variable and use Debug.Print to output it. Post what is produced.

    +1 to Mark's idea also - please could you explain in English what is going on?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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