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

 

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>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

---

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

.

__,_._,___