Results 1 to 7 of 7
  1. #1
    Vic is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    4

    Different results in queries

    Hello,
    I have a database to value real estate. Each property has a set of attributes, like type of walls, type of basement, etc and each attribute has a different cost.



    So, if I want to extract the cost for a basement of type "235", I would use the following statement in the sql builder:

    Code:
     
    SELECT 
     PWCCOST.YEAR, 
     PWCCOST.grp, PWCCOST.code, 
     PWCCOST.desc, PWCCOST.csched_01 
    FROM PWCCOST 
    WHERE 
     PWCCOST.grp='BSMT_TYPE' AND 
     PWCCOST.YEAR=Year([Forms]![CompSearch]![AssessmenTDate]) AND 
     PWCCOST.code=[Forms]![CompSearch]![SubjectInfo].[Form]![bsmt_type];
    csched_01 is the field where the cost is stored.
    If I wanted to extract the same cost from a form, I would use the following vba code:

    Code:
    Dim RST As Recordset
        Dim DBS As Database
     
        Set DBS = CurrentDb
        Set RST = DBS.OpenRecordset("SELECT PWCCOST.YEAR, PWCCOST.grp, PWCCOST.code, " _
                                    & "PWCCOST.desc, PWCCOST.csched_01 FROM PWCCOST " _
                                    & "WHERE( ((PWCCOST.grp)='HEATING') AND " _
                                    & "((PWCCOST.YEAR)=" & year([Forms]![CompSearch]![AssessmenTDate]) _
                                    & "AND (PWCCOST.code)='" _
                                    & [Forms]![CompSearch]![SubjectInfo].[Form]![heating] & "'));")
     
        If RST.RecordCount = 1 Then
            Form_SubjectInfo.HeatCost = RST!csched_01
    I use the recordcount property just in case I had duplicates.
    The code used to work fine until a couple of days ago. The recordcount property returned 4 records each time from the form, but was working fine if i used the same sql statement in the sql builder.

    I had not made any changes to the database neither have the users.

    Has anyone ever experienced similar issues? Any ideas as tohow to fix this?

    Any help would be appreciated.

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    For traceability reasons

    Pass the sql statement to a string variable and use this string variable for your recordset.

    Dim strSQL As String
    strSQL = "SELECT ..."
    Set RST = DBS.OpenRecordset(strSQL)

    Then after this line enter Debug.Print strSQL

    When run the results of the sql will apear in the immediate window. Copy and Paste the sql into a new query and see what it gives you. You should be able to see from there what is issue is. If not come back to us.

    David

  3. #3
    Vic is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    4
    David,
    Thanks for replying.
    I followed your suggestion and modified the code so that the sql statement would be placed in a variable.
    This is how the code looks like now:

    Code:
     
    Private Sub GetHeat()
    On Error GoTo ErrorTrap
     
        Dim RST As Recordset
        Dim DBS As Database
        Dim strSQL As String
     
        strSQL = "SELECT PWCCOST.YEAR, PWCCOST.grp, PWCCOST.code, " _
                                    & "PWCCOST.desc, PWCCOST.csched_01 FROM PWCCOST " _
                                    & "WHERE( ((PWCCOST.grp)='HEATING') AND " _
                                    & "((PWCCOST.YEAR)=" & year([Forms]![CompSearch]![AssessmenTDate]) _
                                    & "AND (PWCCOST.code)='" _
                                    & [Forms]![CompSearch]![SubjectInfo].[Form]![heating] & "'));"
        Set DBS = CurrentDb
        Debug.Print strSQL
        Set RST = DBS.OpenRecordset(strSQL)
     
        If RST.RecordCount = 1 Then
            Form_SubjectInfo.HeatingType = RST!desc
            Form_SubjectInfo.HeatCost = RST!csched_01
        Else
            Form_SubjectInfo.HeatingType = "???"
            Form_SubjectInfo.HeatCost = 0
        End If
     Debug.Print RST.RecordCount
     
    ExitError:
        Exit Sub
        Set RST = Nothing
        Set DBS = Nothing
     
    ErrorTrap:
        Debug.Print "SubjectInfo Error on GetHeat()"
        Resume ExitError
    End Sub
    - I now realize I should have used a function instead of a sub. I also have a function that does the same and I obtain the same results. And this sub had been working fine for years! It just quit working on me a few days ago.

    Anyway, here is the sql:

    Code:
     
    SELECT PWCCOST.YEAR, PWCCOST.grp, PWCCOST.code, PWCCOST.desc, PWCCOST.csched_01 FROM PWCCOST WHERE((PWCCOST.grp='HEATING') AND (PWCCOST.YEAR=2009) AND (PWCCOST.code='01'));
    When I run the sql in a query, this is what I get:
    Query1YEARgrpcodedesccsched_012009HEATING01Forced Air2.7
    Just one record.

    However, if you look at the RST.RecordCount property, I have 7 records (I can't if it is possible to open the actual recorset from the code).

    So when running the same sql from code, the recordset returns 7 records. When running the same sql statment from a query editor, I only get 1 record, as expected.

    At this point I'm lost and frustrated...

    Thanks again, David.
    Last edited by Vic; 11-02-2009 at 02:34 PM. Reason: cleaning up code

  4. #4
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Add the following code below this line

    Set RST = DBS.OpenRecordset(strSQL)
    Code:
     
    Dim sItem As String
    Dim x as Integer
     
    If Not RST.EOF And Not RST.BOF Then
       Do Until RST.EOF
          For x = 0 To RST.Fields.Count - 1
             sItem = sItem & RST(x) & vbTab        
          Next
          Debug.Print sItem
          sItem = ""
          RST.MoveNext
       Loop
    End If
    Now check what the recordset s returning.

    David

  5. #5
    Vic is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    4
    David, thank you again for your response.

    I did what you asked, and inserted a rst.recordcount statement before and after the loop.

    Remember that running the same query returns only one record (there is only 1 cost for 2009 for that particular heating type)

    Going into the loop, the recordcount property returns 7 items.
    The sItem variable is then filled with the information contained in the fields as expected. When this variable is printed out, it returns the same as the query in the sql editor.

    When the cursor moves next (RST.MoveNext) to the 2nd record out of the original 7 in the recordset, and the code checks for EOF, the EOF property returns true and the code stops executing.

    The rst.recordcount line I inserted at the end, now returns 1!

    Any ideas?

    Thanks again for your help, David!

  6. #6
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    When opening a recordset the recordset does not actually know how many records it contains until it gets to the last record in the recordset therefore as soon as you open it and check that it is not EOF or BOF if you then issue a Rs.MoveLast then an Rs.Recordcount the Rs.RecordCount will be correct.

    Code:
     
    If Not RST.EOF And Not RST.BOF Then
    
     ''''Insert Here
       RST.MoveLast
       MsgBox RST.RecordCount & " record(s) found"
       RST.MoveFirst
     ''''
       Do Until RST.EOF

  7. #7
    Vic is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    4
    That worked! I changed the code and instead of using a sub I'm using a function with your suggestions to return the values. Thank you, David!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-24-2011, 04:11 PM
  2. Weird Query results
    By UCBFireCenter in forum Queries
    Replies: 0
    Last Post: 10-06-2009, 03:38 PM
  3. display query results in a form
    By P5C768 in forum Queries
    Replies: 3
    Last Post: 08-14-2009, 03:02 PM
  4. Combine Query Results to One Table
    By pr4t3ek in forum Queries
    Replies: 0
    Last Post: 12-19-2008, 06:37 AM
  5. Entering query results in a form
    By marcello.dolcini in forum Forms
    Replies: 0
    Last Post: 04-15-2007, 06:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums