domain aggegate function

  • Thread starter Thread starter Vsn
  • Start date Start date
V

Vsn

Hello all,

Can somebody tell me if and how it is possible to change the domain from an
aggregate function for an SQL string?

I think about something as below but cant get it to work:-

MySql = " " _
& "SELECT tblNumbers.fID, tblNumbers.fNum " _
& "FROM tblNumbers " _
& "ORDER BY tblNumbers.fNum " _
& "WITH OWNERACCESS OPTION;"

DCount ("fID",MySql,"fNum>100")


Thx for any suggestions,
Ludovic
--
 
Vsn said:
Hello all,

Can somebody tell me if and how it is possible to change the domain
from an aggregate function for an SQL string?

I think about something as below but cant get it to work:-

MySql = " " _
& "SELECT tblNumbers.fID, tblNumbers.fNum " _
& "FROM tblNumbers " _
& "ORDER BY tblNumbers.fNum " _
& "WITH OWNERACCESS OPTION;"

DCount ("fID",MySql,"fNum>100")


Thx for any suggestions,
Ludovic

The domain functions cannot use a SQL string like that. It would not be
difficult to build custom functions that did though. Since you're already
going to the trouble to build a SQL string, most people would just use a
Recordset though since that would only require a few more lines of code.
 
Vsn said:
Rick,

Thx, for your always valuable reply. I have been thinking about it
now for a couple of days and have to state I am not sure if I do
understand you rightly.

Do you say it would be faster/easier to write a record-set to count a
number of records that pass a criterea? So I sould write a function
that does return the counted value and in this function create and
destroy the recordset used each time? If that is the case, I better
update myself on record set coding, not my strongest point I believe.

SQL code is easy, since I mostly use a query to create the SQL, than
I copy and paste it to a tool I made and this will copy the VBA code
of the string back to the clipboard, ready for pasting.

Thx,
Ludovic
 
Vsn said:
Rick,

Thx, for your always valuable reply. I have been thinking about it
now for a couple of days and have to state I am not sure if I do
understand you rightly.

Do you say it would be faster/easier to write a record-set to count a
number of records that pass a criterea? So I sould write a function
that does return the counted value and in this function create and
destroy the recordset used each time? If that is the case, I better
update myself on record set coding, not my strongest point I believe.

SQL code is easy, since I mostly use a query to create the SQL, than
I copy and paste it to a tool I made and this will copy the VBA code
of the string back to the clipboard, ready for pasting.

Quick example with your SQL (or a variation on it).

Dim sql as String
Dim Cnt as Long
sql = "SELECT Count(fID) FROM tblNumbers"
cnt = CurrentDB.OpenRecordset(sql).Fields(0)

Can you see how the SQL could use Count() or Max() or Min() etc.? This
example had no WHERE clause, but that would be easy enough to add.
 
Rick,

Thx, for your always valuable reply. I have been thinking about it now for a
couple of days and have to state I am not sure if I do understand you
rightly.

Do you say it would be faster/easier to write a record-set to count a number
of records that pass a criterea? So I sould write a function that does
return the counted value and in this function create and destroy the
recordset used each time? If that is the case, I better update myself on
record set coding, not my strongest point I believe.

SQL code is easy, since I mostly use a query to create the SQL, than I copy
and paste it to a tool I made and this will copy the VBA code of the string
back to the clipboard, ready for pasting.

Thx,
Ludovic
 
Rick,

Got it now. Looks quit clever I think. I will try to use and extend your
example.

Thx,
Ludovic
 
Back
Top