Wow. Thanks a lot. I works like a charm.
Nimish
From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Sunday, January 29, 2017 8:33 AM
Subject: 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
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
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
(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/
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: Parikh Nimish <parikhnd@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
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.
.
__,_._,___
Tidak ada komentar:
Posting Komentar