FIX: Domain Functions Available Only Within SQL Statement

The information in this article applies to:

- Standard and Professional Editions of Microsoft Visual Basic

  programming system for Windows, version 3.0

SYMPTOMS

If you try to use the domain aggregate function examples provided in the Microsoft Visual Basic version 3.0 for Windows Help file, you will receive this error message:

   Reference to undefined Function or array

CAUSE

The examples for the domain aggregate functions are incorrect. The domain aggregate functions, like the SQL aggregate functions, can be used only within an SQL statement.

WORKAROUND

Use the domain aggregate functions within an SQL statement, as in the following example. Enter the following as one, single line:

   Set Dn = Db.CreateDynaset("Select DAvg(""AU_ID"", ""AUTHORS"")
      FROM Authors")

STATUS

Microsoft has confirmed this to be a bug in the Visual Basic version 3.0 Help file. This problem has been corrected in Visual Basic version 4.0.

MORE INFORMATION

Step-by-Step Example

The following example demonstrates how to print to the form an average of all the AU_ID values in the Authors table from the BIBLIO.MDB database that comes with Microsoft Visual Basic version 3.0 for Windows:

1. Start Visual Basic or from the File menu, choose New Project if Visual Basic is already running. Form1 is created by default.

2. Add the following code to the Click event of Form1:

      Sub Form_Click()
         Dim Db As Database
         Dim Dn As Dynaset
   
         Set Db = OpenDatabase("C:\VB\BIBLIO.MDB")
         ' Note: enter the following Set Dn code as one, single line.
         Set Dn = Db.CreateDynaset("Select DAvg(""AU_ID"", ""AUTHORS"")
            FROM Authors")
            Print Dn(0) ' This is the equivelant of
                        ' Form1.Print Dn.Fields(0).Value
         ' It is always a good idea to close the database objects:
         Dn.Close
         Db.Close
   
      End Sub
   
   

3. Run the example. Then click the form.

The Following are the Domain Aggregate Functions:

   DAvg
   DCount
   DFirst
   DLast
   DLookup
   DMin
   DMax
   DStDev
   DStDevP
   DSum
   DVar
   DVarP