Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    St Andrews, Fife, Scotland
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    writing a user defined aggregate function (Access2000)

    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

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: writing a user defined aggregate function (Access2000)

    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 .
    Attached Files Attached Files
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: writing a user defined aggregate function (Access2000)

    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).

  4. #4
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: writing a user defined aggregate function (Access2000)

    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

  5. #5
    New Lounger
    Join Date
    Jan 2003
    Location
    St Andrews, Fife, Scotland
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: writing a user defined aggregate function (Access2000)

    Thank for all you invaluable help - problem solved

    Best wishes

    Bernie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •