Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

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.
0
bfuchs
Asked:
bfuchs
  • 2
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
start with this

Select Count(A.Dates) as CountOfSchedules
from
(select TableX.Dates From TableX
  Group By TableX.Dates
) As A
0
 
bfuchsAuthor Commented:
hi,
and how do i get this results into a variable?
0
 
Rey Obrero (Capricorn1)Commented:
save the above query as QCount

open it as recordset

dim rs as dao.recordset, xCount as integer
set rs=currentdb.openrecordset("QCount")

xCount=rs!CountOfSchedules
0
 
bfuchsAuthor Commented:
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now