domain function question
Hi all,
I need to know what is the most efficient way to get the following in vba
count of distinct dates for certain criteria.
for example, how many distinct dates are scheduled for this week.
two schedule records for Sunday are considered 1, while Sun, Mon, Tue are counted as 3.
I know I can do it in a saved distinct query and use DCount of that query, however wonder if that is the most efficient way of accomplishing it, as i am dealing with a very large table.
I need to know what is the most efficient way to get the following in vba
count of distinct dates for certain criteria.
for example, how many distinct dates are scheduled for this week.
two schedule records for Sunday are considered 1, while Sun, Mon, Tue are counted as 3.
I know I can do it in a saved distinct query and use DCount of that query, however wonder if that is the most efficient way of accomplishing it, as i am dealing with a very large table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
save the above query as QCount
open it as recordset
dim rs as dao.recordset, xCount as integer
set rs=currentdb.openrecordset
xCount=rs!CountOfSchedules
open it as recordset
dim rs as dao.recordset, xCount as integer
set rs=currentdb.openrecordset
xCount=rs!CountOfSchedules
ASKER
Thanks allot Ray, that worked, however just wonder if the following approach would perform faster?
create a SQL function that accepts a int variable and returns a int, then have this function being called from the app.
p.s. its a ms access front end application linked to SQL Server back end.
create a SQL function that accepts a int variable and returns a int, then have this function being called from the app.
p.s. its a ms access front end application linked to SQL Server back end.
ASKER
and how do i get this results into a variable?