Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2002
    Posts
    75

    Unanswered: Query Returns Strange Result

    Hi Everyone!

    Could you please take a look at the code below. The code runs just fine, but the recordset returns 3 records, while logically (based on table data) it is supposed to return only 1 record. When I run the same query in Query Designer, the result is one record.

    The table has four fields, and all of them are needed in the returned rs. I tried using * instead of listing all of the fileds -- the result is the same.

    Any help will be greatly appreciated!!!

    Vasilyok
    Code:
    strSQL = strSQL & "SELECT tbl.PID, tbl.Unit, "
    strSQL = strSQL & "tbl.Contact, tbl.BackUp FROM tbl "
    strSQL = strSQL & "WHERE (((tbl.PID)<>" & Me.cboPID
    strSQL = strSQL & ") AND ((tbl.Unit)='" & Me.cboUnit
    strSQL = strSQL & "') AND ((tbl.Contact)=-1));"
    
    Set rs = CurrentDb().OpenRecordset(strSQL)

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Have you done a

    Debug.Print strSQL

    and examined the resulting string in the immediate window to ensure it's coming out the same as the query run from the QBE?
    Paul

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'd be inclined to side with pbaldy. Comboboxes can be deceptive with what value you're really getting if you have a multi-column combo with a non-intuitive bound column.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Apr 2002
    Posts
    75
    Thanks for your replies!

    Yes, I did use debug.print strSQL -- the string is correct.

    What I also did is print out the recordset content:

    for i=1 to .recordcound
    debug.print .f1, .f2, f3, f4
    next i

    The recordcount is always 3, though based on data, it should be 1. The debug prints the same record 3 times.

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You should be able to trace your problem to one of the 3 fields in your where statement. Two of the three records being returned are "wrong", and don't meet the criteria you expect. You just need to figure out which column it is, and then adjust the WHERE clause.
    Inspiration Through Fermentation

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Vasilyok,

    I find it strange that the first line in your SQL string begins with

    Code:
    strSQL = strSQL & "SELECT tbl.PID, tbl.Unit, "
    That sounds to me like you're appending it to an existing string. If that's the real first line of code, it should say

    Code:
    strSQL = "SELECT tbl.PID, tbl.Unit, "
    Also, have you tried using a GROUP BY clause? If it's repeating the same record 3 times, that'll get rid of it.

    Sam

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Sam LAndy
    I find it strange that the first line in your SQL string begins with
    Code:
    strSQL = strSQL & "SELECT tbl.PID, tbl.Unit, "
    I agree but if it was preceded by
    Code:
    strSQL = ""
    Then that'd be fine. That's something I use out of force of habit

    EDIT: Just to re-write the full badger (I hate the over use of parens)
    Code:
    strSQL = ""
    strSQL = strSQL & " SELECT tbl.PID, tbl.Unit, tbl.Contact, tbl.BackUp"
    strSQL = strSQL & " FROM tbl"
    strSQL = strSQL & " WHERE tbl.PID <> " & Me.cboPID
    strSQL = strSQL & " AND tbl.Unit = '" & Me.cboUnit & "'"
    strSQL = strSQL & " AND tbl.Contact = -1"
    Last edited by gvee; 07-20-07 at 14:22.
    George
    Home | Blog

  8. #8
    Join Date
    Mar 2006
    Posts
    163
    It doesn't matter strSQL is used in the first line - it doesn't even need to be declared.

    If it is declared it will be an empty string so there would be no need for strSQL.

    And if it isn't declared it would be an empty variant.

    I think we actually need to see some data and/or the resultant SQL.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Woah woah woah!
    Yes it does need to be declared.
    Good programmers program with Option Explicit.

    Norie, what we were given was a snippet of code, if it is a module wide (or public) variable it may still be containing information from it's last use - which is why we don't want to add to it - hence the need to reset it to our new value each time.
    George
    Home | Blog

Posting Permissions

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