Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Unanswered: inconsistant query results

    I am using an input box to get a ID value for a query. On close of the input box a SQL statement is built using the ID in the where clause. This SQL statement is set to a list box's row source and the list box is required.

    The problem is that the results are inconsistant. Using the same ID willnot always get all the results - sometimes it will. You can run it 2, 3,4 - 10 times it doesn't matter sometime it wil be a partial set sometimes the full set.

    This is Acess 2000 on XP pro.

    I can run the SQL statement manually and it always returns the full result set.

    Any ideas???? I am at a loss.

    Thanks

    Here is the code:

    kitid = CLng(InputBox("Enter Kit Number to get history for:", "History Info"))

    Set conn = CurrentProject.Connection
    Set cmd.ActiveConnection = conn
    rs.CursorLocation = adUseClient

    frmsort.Visible = False

    strsql = "Select * from source_history where kitid = " & kitid
    cmd.CommandText = strsql
    rs.Open cmd
    If rs.EOF Then
    rs.Close
    MsgBox "That kit does not exists.", vbOKOnly
    Exit Sub
    End If
    rs.Close
    strsql = "SELECT source_history.historyid, source_history.kitid, " & _
    "customer.customername, Nuclide.name, source_history.SerialNumber, " & _
    "source_history.WipeDate, source_history.AnalysisDate, source_history.kitaction, " & _
    "source_history.actiondate " & _
    "FROM (source_history INNER JOIN customer ON source_history.CustomerID = " & _
    "customer.customerid) INNER JOIN Nuclide ON " & _
    "source_history.nuclide = Nuclide.index " & _
    "where kitid = " & kitid & " Order By actiondate"

    lstHistory.RowSource = strsql
    lstHistory.Requery

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    What service pack are you on? Have you tried iy on another box/os?

    Is the count of the partial set always the same?

  3. #3
    Join Date
    Jan 2004
    Posts
    5
    Tried on on both SP3 and no SP works the same. OS = XP Pro, 3 differend boxes. partial set is almost always the same (no 2003 data) from what I can tell.

    Originally posted by Rockey
    What service pack are you on? Have you tried iy on another box/os?

    Is the count of the partial set always the same?

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Can you send me the db or export what is needed for this part?

  5. #5
    Join Date
    Jan 2004
    Posts
    5
    Send me your email, I don't really want to post it.

    I've been programming Access since version 2, I've never seen anything like this.

    b.vandyke@sbcglobal.net

    Thanks


    Originally posted by Rockey
    Can you send me the db or export what is needed for this part?

Posting Permissions

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