Re: [MS_AccessPros] Order By in a VBA causing issues???

 

Nimish-

I'm surprised the first example worked - the query engine must have decided to do a full data scan, so it returned all the records immediately.  In the second query with the Order By, the query engine clearly decided to use the index on the sorted fields, and it stopped after returning the first record it found.  To ensure you get an accurate count, you must force it to fetch all the records by using a MoveLast, like this:

Public Function CombinedConstAlias(AliasCode As String) As String


    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sqlstr As String
    
    Set db = CurrentDb()
    sqlstr = "SELECT AliasCode, AttributeCode, AttributeValueCode"
    sqlstr = sqlstr & " FROM qryAliasConstructionDetail WHERE AliasCode = "
    sqlstr = sqlstr & """" & AliasCode & """"
    sqlstr = sqlstr & " ORDER BY AttributeCode, AttributeValueCode;"
    
    Set rs = db.OpenRecordset(sqlstr, dbOpenDynaset)
    ' Force to end of recordset to get accurate count

    rs.MoveLast
    CombinedConstAlias = CStr(rs.RecordCount)
    
    Set rs = Nothing
    Set db = Nothing


End Function

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Jan 29, 2017, at 3:24 PM, Parikh Nimish parikhnd@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Friends,
I started learning Access VBA, very recently and to my annoyance, I stumbled upon very step. I'll first explain the situation I am in.
I am trying to combine values into one (that I've not started writing in the code yet) using user defined function CombineConstAlias() function.
When I execute function ?CombineConstAlias("F01") with the following code I get correct record count.
Public Function CombinedConstAlias(AliasCode As String) As String


    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sqlstr As String
    
    Set db = CurrentDb()
    sqlstr = "SELECT AliasCode, AttributeCode, AttributeValueCode"
    sqlstr = sqlstr & " FROM qryAliasConstructionDetail WHERE AliasCode = "
    sqlstr = sqlstr & """" & AliasCode & """;"
    
    
    Set rs = db.OpenRecordset(sqlstr, dbOpenDynaset)
    
    CombinedConstAlias = CStr(rs.RecordCount)
    
    Set rs = Nothing
    Set db = Nothing


End Function




However, if I run same function with ORDER BY clause added, I get only one record count. I am intrigue by this strange result.


Public Function CombinedConstAlias(AliasCode As String) As String


    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sqlstr As String
    
    Set db = CurrentDb()
    sqlstr = "SELECT AliasCode, AttributeCode, AttributeValueCode"
    sqlstr = sqlstr & " FROM qryAliasConstructionDetail WHERE AliasCode = "
    sqlstr = sqlstr & """" & AliasCode & """"
    sqlstr = sqlstr & " ORDER BY AttributeCode, AttributeValueCode;"
    
    Set rs = db.OpenRecordset(sqlstr, dbOpenDynaset)
    
    CombinedConstAlias = CStr(rs.RecordCount)
    
    Set rs = Nothing
    Set db = Nothing


End Function


Am I missing something here?


Nimish


[Non-text portions of this message have been removed]




------------------------------------
Posted by: Parikh Nimish <parikhnd@yahoo.com>
------------------------------------


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

Yahoo Groups Links

<*> To visit your group on the web, go to:
   http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
   Individual Email | Traditional

<*> To change settings online go to:
   http://groups.yahoo.com/group/MS_Access_Professionals/join
   (Yahoo! ID required)

<*> To change settings via email:
   MS_Access_Professionals-digest@yahoogroups.com
   MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
   MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
   https://info.yahoo.com/legal/us/yahoo/utos/terms/

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___