Solved

domain function question

Posted on 2014-02-19
4
291 Views
Last Modified: 2014-02-20
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
Comment
Question by:bfuchs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39871944
start with this

Select Count(A.Dates) as CountOfSchedules
from
(select TableX.Dates From TableX
  Group By TableX.Dates
) As A
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39871950
hi,
and how do i get this results into a variable?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39871971
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
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 39874778
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question