domain aggegate function

[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
--

 

[Rick Brandt]

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.

 

[Rick Brandt]

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.

 

[Vsn]

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]

Rick,

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

Thx,
Ludovic