writing a user defined aggregate function (Access2000)

Topic

New Reply

Good Day,

This is my first post so apologies for naivety or breaking protocol. I think I want to write an aggregate function in VBA that I can call in MSAccess SQL. An example

x name
— ——-
a Alice
b Alice
a Fred
a Fred

Select name, myFunction(x) from MyTable group by name;

Suppose I want myFunction to return 1 if

Reply | Quote

Replies

I don’t think you need a function to do this job.

I attach a db that does it with two select queries.

First query selects all people that at least one “b” – Uses distinct in the sql to show people only once.

Second Query does a right join from first query back to the table, and uses a caculated field to show 1 or 0 .

Reply | Quote

You should be able to do this in a query. Do you know about inner and outer joins? (Right click on the line joining two tables when you have a query open in design mode and see what is possible).

For a function the following should be OK
public function MyFunc(var as Variant)

(Don’t forget to test that var exists in your processing).

Reply | Quote

As Andy and John have pointed out, you wouldn’t need a function to do this. However, your question was how to do this with a function.

You can definitely write VBA functions and use them in your querries. If a normal query can produce the same results, and thus you would want to use the query version because it will run faster. (Not that code is slow, it just runs slower when the Jet Engine has to run your code each time it needs a value, versus using it’s own capabilities.)

There is a exception to that rule, though…at least I have an exception for it. When you run a query, you cannot ‘step’ through each record as it is pulled up, versus when you have your query run a user defined function, you can put a break in your code, to step through the function, effectively stepping through your query’s data pull.

There are times where using a user defined function is the only option. For instance, I wrote a query a few weeks ago for an Email spamming utility. The utility doesn’t remove the email box, it only filtered the message, and replaces the message with a default message. So I wrote a VB routine that people can ‘turn on’ for their account, which deletes messages that contain only that message every 15 minutes. Since not everyone has that feature turned on, and I wanted a report showing the total number of ‘filtered’ emails, along with total numbers deleted, I needed to count the number of quarantined messages in a folder, thus I had to do some file counting. Not something you can do with SQL, so I wrote a function to count the files, and added it to the query.

If you still want to go the Function route, can you post the table structure or SQL that pulls the ‘group by’ data?

Drew

Reply | Quote

Thank for all you invaluable help – problem solved

Best wishes

Bernie

Reply | Quote