SQL Problem, Undefined Function in Expression

[barryna]

I have the following query in Access 2000:

SELECT YieldGrade AS TheValue, Count(Animals_Harvest.YieldGrade) AS TheCount, Format(Count(Animals_Harvest.YieldGrade)/DCount(&quot;Animals_Harvest.YieldGrade&quot;,&quot;Animals_Harvest&quot;,&quot;Animals_Harvest.KillDate>=#6/15/2003# And Animals_Harvest.KillDate<=#6/20/2003# AND YieldGrade IS NOT NULL&quot*100,&quot;###.00&quot AS ThePct, Format(DCount(&quot;Animals_Harvest.YieldGrade&quot;, &quot;Animals_Harvest&quot;, &quot;YieldGrade = &quot; & YieldGrade) / DCount(&quot;Animals_Harvest.YieldGrade&quot;, &quot;Animals_Harvest&quot;, &quot;YieldGrade IS NOT NULL&quot * 100, &quot;###.00&quot As TheStd From Animals_Harvest WHERE (((Animals_Harvest.KillDate) >= #6/15/2003# And (Animals_Harvest.KillDate) <= #6/20/2003#)) And YieldGrade Is Not Null GROUP BY YieldGrade;

And it works fine in Access. I am transfering it over into my VB program and it doesn't work. I get &quot;Undefined Function 'DCount' in Expression&quot;. What does that mean? I am using DAO 3.6 Object Library for data access. Is DCount, DAvg functions that can be used in Access 2000, but not in DAO?

Can't figure it out.

 

[paulbent]

I believe in DAO, you just need to drop the 'D'. The aggregate functions are named Avg, Count, Max, Min and Sum afaik.

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[barryna]

by dropping the 'D', will it still do what I need it to do? i.e. a domain aggregate funtion?

 

[Golom]

I'm not sure of DAO is doing something to DCount but I occasionally get one of those &quot;... works in Access but not in VB situations ...&quot; I've found that by creating a new QueryDef, setting its SQL property to what I want, adding it to the querydefs collection and then opening the recordset from the query will work where identical SQL opened with a &quot;Set rs = db.OpenRecordset(SQL)&quot; statement doesn't work.

No guarantees but give it a try.

 

[barryna]

Also, I have tried this code:

On Error Resume Next
AnimalDB.QueryDefs.Delete (&quot;qryTemp&quot
DoEvents
On Error GoTo Summ2_Error

[my SQL from above]

Set qry = AnimalDB.CreateQueryDef(&quot;qryTemp&quot;, SQL)
qry.Close
DoEvents
Set rs3 = AnimalDB.OpenRecordset(&quot;qryTemp&quot;, dbOpenSnapshot)



The created query in Access runs fine from access, but when trying to run the query in vb 6.0, I get the same error, &quot;Undefined function 'DCount' in Expression&quot;.

 

[barryna]

dropping the 'D' doesn't work, get errors

 

[Golom]

Can you change the &quot;DCount&quot; functions to sub-selects? For example


DCount(&quot;Animals_Harvest.YieldGrade&quot;,&quot;Animals_Harvest&quot;,&quot;Animals_Harvest.KillDate>=#6/15/2003# And Animals_Harvest.KillDate<=#6/20/2003# AND YieldGrade IS NOT NULL&quot

becomes

(Select Count(*) From Animals_Harvest Where KillDate>=#6/15/2003# And KillDate<=#6/20/2003# AND YieldGrade IS NOT NULL)

 

[CCLINT]

DCount() is not a JET function.
It is an ACCESS function.
So, you cannot use it this way.

You will need to create a (parameter) query in ACCESS and call this instead.
DCount actually produces a seperate query, so it slows thing down alot, and is bad practice to use in a SQL Statement.

The way around it here is to use the Count(*) function, if the field is in the same table as in the FROM clause:

COUNT(*) or COUNT(SomeField)

 

[CCLINT]

>Can you change the &quot;DCount&quot; functions to sub-selects

You got it. A DCount() function is like a sub-select.
Not needed unless the sub-select needs to be ran on a seperate table (usually in a WHERE clause), but under certain conditions it may be needed to run on the same table.
This is, as far as I can quickly determine, not one of those cases.

 

[paulbent]

I did a quick search of my MSDN CDand found this under DAO 3.6:

>>aggregate function

A function, such as Sum, Count, Avg, and Var, that you can use to calculate totals. In writing expressions and in programming, you can use SQL aggregate functions (including the four listed here) and domain aggregate functions to determine various statistics.<<

So they appear to be supported but the only other references to the D functions I could find were for Access. There was nothing else to suggest they can be used in VB or VC with DAO.

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[barryna]

My code works fine in VBA code in the background of Access 2000 using a DAO.Recordset. But not in VB 6.0. Am I missing a reference or something?

Oh, and CCLINT, my field in the query, &quot;TheStd&quot;, is a sub-select that needs to be ran with a different WHERE clause. i.e. getting the standard from the entire database.

 

[barryna]

If found this info on my MSDN CDs under Office 2000 Documentation:


Domain Aggregate Functions

Aggregate functions provide statistical information about sets of records (a domain). For example, you can use an aggregate function to count the number of records in a particular set of records, or to determine the average of values in a particular field.

The two types of aggregate functions, domain aggregate functions and SQL aggregate functions, provide similar functionality but are used in different situations. The SQL aggregate functions can be included in the syntax of an SQL statement, but can't be called directly from Visual Basic. Conversely, the domain aggregate functions can be called directly from Visual Basic code. They can also be included in an SQL statement, but an SQL aggregate function is generally more efficient.

If you are performing statistical calculations from within code, you must use the domain aggregate functions. You can also use the domain aggregate functions to specify criteria, update values, or create calculated fields in a query expression. You can use either the SQL aggregate or domain aggregate functions in a calculated control on a form or report.

The domain aggregate functions include:

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



From what I can understand from this part from above:

&quot;Conversely, the domain aggregate functions can be called directly from Visual Basic code. They can also be included in an SQL statement&quot;

It tells me it should work, but not sure. Doesn't say anything about DAO 3.6. DAO is the data access created by microsoft for access, isn't it? So, I would assume domain aggregate functions should work.

 

[barryna]

Also found this:


SQL Aggregate Functions

The SQL aggregate functions are similar to the domain aggregate functions, but they are used in different contexts. In Microsoft Access, you can use the SQL aggregate functions in an SQL statement that you create in SQL view of the Query window, or in an SQL statement within Visual Basic code. Conversely, domain aggregate functions can be called directly from Visual Basic code.

You can use both SQL aggregate and domain aggregate functions in query expressions in the query design grid. SQL aggregate functions are most useful in totals queries and crosstab queries.

You can also use both types of aggregate functions in a calculated control on a form or report.



What does &quot;domain aggregate functions can be called directly from Visual Basic code.&quot; mean? Does that mean they can or can't be used in SQL statements?

 

[Golom]

I think what CCLINT was saying is that ACCESS supports DCount, not JET. Because of that, running it inside Access (the application) works but running it within JET (the DB Engine) doesn't. (CCLINT ... correct me if I didn't get your intent right.)

As to the sub select for &quot;TheStd&quot;

Format(
DCount (&quot;Animals_Harvest.YieldGrade&quot;, &quot;Animals_Harvest&quot;, &quot;YieldGrade = &quot; & YieldGrade) /
DCount(&quot;Animals_Harvest.YieldGrade&quot;, &quot;Animals_Harvest&quot;, &quot;YieldGrade IS NOT NULL&quot * 100, &quot;###.00&quot As TheStd

should become

Format(
(SELECT Count(*) FROM Animals_Harvest WHERE YieldGrade = &quot; & YieldGrade & &quot /
(SELECT Count(*) FROM Animals_Harvest WHERE NOT (YieldGrade IS NULL)) * 100, &quot;###.00&quot As TheStd

 

[CCLINT]

This is the way I have understood how MS ACCESS works these (its own) domain aggregate functions:

DCount() is &quot;nothing more&quot; than this(actually, a little smarter):

Function DCount(ByVal Field As String, ByVal Table As String, Optional ByVal Criteria As String) As Long
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sqlString As String
    On Error GoTo ErrHandler
    If Len(Criteria) Then Criteria = " WHERE " & Criteria
    sqlString = "SELECT COUNT([" & Field & "]) As TotalCount FROM [" & Table & "]" & Criteria
    Set db = CurrentDatabase 'Or an OpenDatabase statement
    'Set db = DBEngine.Workspaces(0).OpenDatabase(MDB_PATH & MDB_NAME, False, False, ";pwd=123456789")
    
    Set rs = db.OpenRecordset(sqlString, dbOpenForwardOnly)
    If Not rs.EOF Then
       If Not IsNull(rs.Fields(0).Value) Then DCount = rs.Fields(0).Value
    End If
ErrHandler:
    If Err.Number Then
        MsgBox Err.Number & ": " & Err.Description
    End If
    
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    'If Not db Is Nothing Then db.Close 'comment out for MS ACCESS
    Set db = Nothing
End Function

What MS ACCESS does, (which I could also do in VB - calling them: CCLINT's domain aggregate functions ), is parse the sql statement looking for any of it's own domain aggregate functions prior to passing them to JET.
When it finds one like this, it will execute a seperate query to get the value.
Then it substitutes the function in the sql statement with the return value of the domain aggregate function.
Once it has done this for all MSA domain aggregate functions in the sql statement, it then passes the sql statement to JET.

SELECT Field1, Field2/DCount(...) As Total1, Field3/DCount(...) As Total2, Field4...FROM...

will get passed to JET like

SELECT Field1, Field2/240 As Total1, Field3/240 As Total2, Field4...FROM...

As you can probably see, with alot of domain aggregate functions like this, each calling a seperate query, it can slow things down considerably.
Not only that, but in a multi-user environment, if other users are adding/deleteing records from the same table, and data access takes slightly longer, you may get inaccurate values.
Take for instance the above sql statement:
First the first DCount() will be ran; Then the second will be ran; Then the actual SQL statement will be passed to JET.

Now imagine if another user makes a change to one of the tables where a DCount() is accuring, say, between the first and second DCount().
Each DCount may end up with a count value 1+ more or less than the previous:

SELECT Field1, Field2/240 As Total1, Field3/242 As Total2, Field4...FROM...

I am not 100% sure if a lock is place on the whole prior to executing, or just when the actual recordset is executed. But I think the latter applies.

When you use the sql COUNT() function, the records are first pulled, and then the values calculated. You will not have these, (at least) additional problems)

 

[CCLINT]

barryna, I will have a look at your sql statement tomorrow to see what you are exactly doing and how to help you with this, turning it into a workable statement.

Until then, you could try using a stored query, or use my DCount function (may have to tweak it some):

sqlString = &quot;SELECT YieldGrade AS TheValue, Count(Animals_Harvest.YieldGrade) AS TheCount,&quot; & Format$(DCount(.....etc etc...) & &quot; AS TheStd From Animals_Harvest WHERE (((Animals_Harvest.KillDate) >= #6/15/2003# And (Animals_Harvest.KillDate) <= #6/20/2003#)) And YieldGrade Is Not Null GROUP BY YieldGrade&quot;

 

[barryna]

The following SQL will work except for one thing:

Format((SELECT Count(*) FROM Animals_Harvest WHERE YieldGrade = &quot; & YieldGrade & &quot /
(SELECT Count(*) FROM Animals_Harvest WHERE NOT (YieldGrade IS NULL)) * 100, &quot;###.00&quot As TheStd


The 'YieldGrade = &quot; & YieldGrade' part of the SQL will not work cause YieldGrade is not a vb variable, it is part of the SQL, and you cannot do this SQL statement first without knowing what YieldGrade is from the main part of the SQL statement. Therefore, neither Golom's or CCLINT's DCount function will work in this instance.

 

[CCLINT]

er, that

(SELECT Count(*) FROM Animals_Harvest WHERE YieldGrade = &quot; & YieldGrade & &quot/(SELECT Count(*)...)


looks to me nothing more than doing:

..., Count(*)/(SELECT Count(*)...)

Or

...,Count(*)/&quot; & DCount(...)

Excluding from the sub-query/DCount the Date criteria.
(The percent the YieldGrade Group is of ALL records (excluding NULLS)