Access97: User defined variant function treated as string in query 
Author Message
 Access97: User defined variant function treated as string in query

This is a problem which has always troubled me.

If I declare a public VBA function of type variant and use it in an SQL
statement, Jet always treats the results as a string variant rather than a
numeric data type.
The same is true of the (pseudo) VBA function Nz() - the result is always
handled as a text field.

    (Oh and by the way, why is Nz() a "pseudo" VBA function in DAO -
sometimes it is recognised like Left(), sometimes it is not - generating a
"Can't use user-defined function in DAO" error)

Here is an example:

Within an SQL statement, I want to add two numeric results fields together.
If BOTH fields are Null, I want the result to be Null. If NEITHER field is
null, I want the result to be the sum of the two numbers. If either of the
fields is null, I want the result to be the non null value.

This is synonomous with the Sum() SQL aggregate function. It returns Null if
all its inputs are Null but returns the sum of any non-null inputs.

I cannot think of a way of achieving the functionality directly from SQL/VBA
statements.

If the numeric fields are called TotalSales and TotalForecast, the following
occurs

[TotalSales]+[TotalForecast]     returns null if either term is null
Nz([TotalSales])+Nz([TotalForecast])     returns sum as a text field
Nz([TotalSales],0)+Nz([TotalForecast],0)     returns sum as a text field
(i.e. treats Nz() as a string function even if second parameter is numeric)
Val(Nz([TotalSales])+Nz([TotalForecast]))    returns sum as a numeric field
but returns 0 if both terms are Null (rather than Null)

To date, I have used the last of these syntaxes, but I have never been happy
with it. I want to see a blank (null) if no sales or forecast transactions
exist and a zero only if transactions exists but sum to zero. Now I have hit
a requirement which this does not meet.

I have written the following function to attempt to achieve the desired
result:

Function SumNull(ParamArray Val() As Variant) As Variant
' sum a number of values, return Null if all are null, else return sum
    Dim l As Long

    For l = 0 To UBound(Val)
        If Not IsNull(Val(l)) Then
            SumNull = SumNull + Val(l)
        End If
    Next l
    If IsEmpty(SumNull) Then SumNull = Null

End Function

This works fine in report controls but if I use it in the underlying query,
the result is a text field (which cannot then be Sum()'d in a report footer)

In a report control source

    =SumNull([TotalSales],[TotalForecast])

works OK

In an SQL statement

    SELECT [TotalSales], [TotalForecast],
SumNull([TotalSales],[TotalForecast]) AS TotalVal ...

works but TotalVal is a text field which may not be used in aggregate
controls.

How can I get Jet to do a sum on two or more numeric fields and yield Null
only if all terms are Null?

Best Regards

Neil
--------------------------------------------------------
  Neil Sargent
  Smart IT

--------------------------------------------------------



Sat, 27 Sep 2003 07:04:27 GMT  
 Access97: User defined variant function treated as string in query


Quote:
>If I declare a public VBA function of type variant and use it in an SQL
>statement, Jet always treats the results as a string variant rather than a
>numeric data type.

Jet cannot work with variants directly, it has to make some assumptions
about the returned variant in this case. String seems to be the most
"generic" type, I guess that's why they chose it. If you need a number, you
should probably type your function accordingly and not rely on Jet
guessing, whatever that might actually be.

Quote:
>    (Oh and by the way, why is Nz() a "pseudo" VBA function in DAO -
>sometimes it is recognised like Left(), sometimes it is not - generating a
>"Can't use user-defined function in DAO" error)

Nz() is a method of Access.Application, it has nothing to do with VBA or
DAO. If you are running the query within Access, Expression Service should
resolve it for you. If the query is run let's say through ODBC, Access is
not in the picture and Nz() appears to be a UDF, which cannot be used in
this way.

If you are getting this error in Access, I would check references and play
with /decompile.

As far as your questions about Nulls propagation, why not use aggregate Sum
if it works the way you want? It should also be faster than any VBA
function. Otherwise you might try iif() but that will quickly get out of
hand as the number of terms increases.

--
(remove a 9 to reply by email)



Sat, 27 Sep 2003 07:52:44 GMT  
 Access97: User defined variant function treated as string in query
The query compiler decides what type of data it's dealing with at
compile time.  This is not left for run-time.  Also, Nz is not a
pseudo-VBA function, it is a plain old VBA function.  IIf is processed
directly by the query compiler, though and behaves differently than in
VBA code.

Try a query of IIf(<integerfield> Is Not Null, <integerfield>, 0) and
you will see that the result is numeric because the query compiler can
determine that bot the True and False parts are known to be of integer
type.  Note that in SQL, you can also use a 2-argument form of IIf to
handle cases where the False part should be null without changing the
output column type to string.  If you were to use Null as the False
part argument, you would get a string because the query compiler
doesn't know the data type of Null.

So - back to your UDF.  From the wording of your post, I assume you
are returning a variant because the result may be either numeric or
Null and those are the only options.  I'm going to assume you're
dealing with Integer here, but you can translate the following for
whatever type you are actually returning.

IIf(<udfcall> Is Not Null, CInt(<udfcall>))

This appears inefficient because the UDF may be called twice, but I
have found that the optimizer will assume (perhaps incorrectly) that
the same UDF call with the same parameter will return the same result
and only calls the UDF once, using its return value in both places.
You can use debug.print code in your UDF code to test this.

On Tue, 10 Apr 2001 00:04:27 +0100, "Neil Sargent"

Quote:

>This is a problem which has always troubled me.

>If I declare a public VBA function of type variant and use it in an SQL
>statement, Jet always treats the results as a string variant rather than a
>numeric data type.
>The same is true of the (pseudo) VBA function Nz() - the result is always
>handled as a text field.

>    (Oh and by the way, why is Nz() a "pseudo" VBA function in DAO -
>sometimes it is recognised like Left(), sometimes it is not - generating a
>"Can't use user-defined function in DAO" error)

>Here is an example:

>Within an SQL statement, I want to add two numeric results fields together.
>If BOTH fields are Null, I want the result to be Null. If NEITHER field is
>null, I want the result to be the sum of the two numbers. If either of the
>fields is null, I want the result to be the non null value.

>This is synonomous with the Sum() SQL aggregate function. It returns Null if
>all its inputs are Null but returns the sum of any non-null inputs.

>I cannot think of a way of achieving the functionality directly from SQL/VBA
>statements.

>If the numeric fields are called TotalSales and TotalForecast, the following
>occurs

>[TotalSales]+[TotalForecast]     returns null if either term is null
>Nz([TotalSales])+Nz([TotalForecast])     returns sum as a text field
>Nz([TotalSales],0)+Nz([TotalForecast],0)     returns sum as a text field
>(i.e. treats Nz() as a string function even if second parameter is numeric)
>Val(Nz([TotalSales])+Nz([TotalForecast]))    returns sum as a numeric field
>but returns 0 if both terms are Null (rather than Null)

>To date, I have used the last of these syntaxes, but I have never been happy
>with it. I want to see a blank (null) if no sales or forecast transactions
>exist and a zero only if transactions exists but sum to zero. Now I have hit
>a requirement which this does not meet.

>I have written the following function to attempt to achieve the desired
>result:

>Function SumNull(ParamArray Val() As Variant) As Variant
>' sum a number of values, return Null if all are null, else return sum
>    Dim l As Long

>    For l = 0 To UBound(Val)
>        If Not IsNull(Val(l)) Then
>            SumNull = SumNull + Val(l)
>        End If
>    Next l
>    If IsEmpty(SumNull) Then SumNull = Null

>End Function

>This works fine in report controls but if I use it in the underlying query,
>the result is a text field (which cannot then be Sum()'d in a report footer)

>In a report control source

>    =SumNull([TotalSales],[TotalForecast])

>works OK

>In an SQL statement

>    SELECT [TotalSales], [TotalForecast],
>SumNull([TotalSales],[TotalForecast]) AS TotalVal ...

>works but TotalVal is a text field which may not be used in aggregate
>controls.

>How can I get Jet to do a sum on two or more numeric fields and yield Null
>only if all terms are Null?

>Best Regards

>Neil
>--------------------------------------------------------
>  Neil Sargent
>  Smart IT

>--------------------------------------------------------



Sat, 27 Sep 2003 08:06:11 GMT  
 Access97: User defined variant function treated as string in query
Thanks Steve,

Typically I crosstab some sales transactions into monthly sales. If I want
quarterly totals (for example), I must sum three of these monthly sales
columns from my crosstab query. I want Null if there are no transactions for
the quarter (i.e. all three monthly sales fields are Null) or the numeric
sum of any transactions occur in the quarter (the sum of the three monthly
sales fields with Null months ignored)

By entering the following:

IIf(SumNull([AprSales],[MaySales],[JunSales]) Is Not
Null,CDbl(SumNull([AprSales],[MaySales],[JunSales]))) AS Q1Sales

I do get the desired result. But this is so complex - is there no better way
to achieve what I am after?

I would really like to do this without a user defined function.

Effectively I want to say:

IIf (Val1 AND Val2 AND Val3 Is Not Null, Nz(Val1)+Nz(Val2)+Nz(Val3)) AS
TotalVal

However Val1 AND Val2 is Null if either is Null (i.e. if you include a Null
in an expression, the result is Null regardless of the operator)

Any suggestions?

Best Regards
Neil


Quote:
> The query compiler decides what type of data it's dealing with at
> compile time.  This is not left for run-time.  Also, Nz is not a
> pseudo-VBA function, it is a plain old VBA function.  IIf is processed
> directly by the query compiler, though and behaves differently than in
> VBA code.

> Try a query of IIf(<integerfield> Is Not Null, <integerfield>, 0) and
> you will see that the result is numeric because the query compiler can
> determine that bot the True and False parts are known to be of integer
> type.  Note that in SQL, you can also use a 2-argument form of IIf to
> handle cases where the False part should be null without changing the
> output column type to string.  If you were to use Null as the False
> part argument, you would get a string because the query compiler
> doesn't know the data type of Null.

> So - back to your UDF.  From the wording of your post, I assume you
> are returning a variant because the result may be either numeric or
> Null and those are the only options.  I'm going to assume you're
> dealing with Integer here, but you can translate the following for
> whatever type you are actually returning.

> IIf(<udfcall> Is Not Null, CInt(<udfcall>))

> This appears inefficient because the UDF may be called twice, but I
> have found that the optimizer will assume (perhaps incorrectly) that
> the same UDF call with the same parameter will return the same result
> and only calls the UDF once, using its return value in both places.
> You can use debug.print code in your UDF code to test this.

> On Tue, 10 Apr 2001 00:04:27 +0100, "Neil Sargent"

> >This is a problem which has always troubled me.

> >If I declare a public VBA function of type variant and use it in an SQL
> >statement, Jet always treats the results as a string variant rather than
a
> >numeric data type.
> >The same is true of the (pseudo) VBA function Nz() - the result is always
> >handled as a text field.

> >    (Oh and by the way, why is Nz() a "pseudo" VBA function in DAO -
> >sometimes it is recognised like Left(), sometimes it is not - generating
a
> >"Can't use user-defined function in DAO" error)

> >Here is an example:

> >Within an SQL statement, I want to add two numeric results fields
together.
> >If BOTH fields are Null, I want the result to be Null. If NEITHER field
is
> >null, I want the result to be the sum of the two numbers. If either of
the
> >fields is null, I want the result to be the non null value.

> >This is synonomous with the Sum() SQL aggregate function. It returns Null
if
> >all its inputs are Null but returns the sum of any non-null inputs.

> >I cannot think of a way of achieving the functionality directly from
SQL/VBA
> >statements.

> >If the numeric fields are called TotalSales and TotalForecast, the
following
> >occurs

> >[TotalSales]+[TotalForecast]     returns null if either term is null
> >Nz([TotalSales])+Nz([TotalForecast])     returns sum as a text field
> >Nz([TotalSales],0)+Nz([TotalForecast],0)     returns sum as a text field
> >(i.e. treats Nz() as a string function even if second parameter is
numeric)
> >Val(Nz([TotalSales])+Nz([TotalForecast]))    returns sum as a numeric
field
> >but returns 0 if both terms are Null (rather than Null)

> >To date, I have used the last of these syntaxes, but I have never been
happy
> >with it. I want to see a blank (null) if no sales or forecast
transactions
> >exist and a zero only if transactions exists but sum to zero. Now I have
hit
> >a requirement which this does not meet.

> >I have written the following function to attempt to achieve the desired
> >result:

> >Function SumNull(ParamArray Val() As Variant) As Variant
> >' sum a number of values, return Null if all are null, else return sum
> >    Dim l As Long

> >    For l = 0 To UBound(Val)
> >        If Not IsNull(Val(l)) Then
> >            SumNull = SumNull + Val(l)
> >        End If
> >    Next l
> >    If IsEmpty(SumNull) Then SumNull = Null

> >End Function

> >This works fine in report controls but if I use it in the underlying
query,
> >the result is a text field (which cannot then be Sum()'d in a report
footer)

> >In a report control source

> >    =SumNull([TotalSales],[TotalForecast])

> >works OK

> >In an SQL statement

> >    SELECT [TotalSales], [TotalForecast],
> >SumNull([TotalSales],[TotalForecast]) AS TotalVal ...

> >works but TotalVal is a text field which may not be used in aggregate
> >controls.

> >How can I get Jet to do a sum on two or more numeric fields and yield
Null
> >only if all terms are Null?

> >Best Regards

> >Neil
> >--------------------------------------------------------
> >  Neil Sargent
> >  Smart IT

> >--------------------------------------------------------



Sat, 27 Sep 2003 15:15:43 GMT  
 Access97: User defined variant function treated as string in query
Try using the IsNull function, which comes from the VB runtime and is theu
available in queries.

--
MichKa

the only book on internationalization in VB at
http://www.i18nWithVB.com/


Quote:
> Thanks Steve,

> Typically I crosstab some sales transactions into monthly sales. If I want
> quarterly totals (for example), I must sum three of these monthly sales
> columns from my crosstab query. I want Null if there are no transactions
for
> the quarter (i.e. all three monthly sales fields are Null) or the numeric
> sum of any transactions occur in the quarter (the sum of the three monthly
> sales fields with Null months ignored)

> By entering the following:

> IIf(SumNull([AprSales],[MaySales],[JunSales]) Is Not
> Null,CDbl(SumNull([AprSales],[MaySales],[JunSales]))) AS Q1Sales

> I do get the desired result. But this is so complex - is there no better
way
> to achieve what I am after?

> I would really like to do this without a user defined function.

> Effectively I want to say:

> IIf (Val1 AND Val2 AND Val3 Is Not Null, Nz(Val1)+Nz(Val2)+Nz(Val3)) AS
> TotalVal

> However Val1 AND Val2 is Null if either is Null (i.e. if you include a
Null
> in an expression, the result is Null regardless of the operator)

> Any suggestions?

> Best Regards
> Neil



> > The query compiler decides what type of data it's dealing with at
> > compile time.  This is not left for run-time.  Also, Nz is not a
> > pseudo-VBA function, it is a plain old VBA function.  IIf is processed
> > directly by the query compiler, though and behaves differently than in
> > VBA code.

> > Try a query of IIf(<integerfield> Is Not Null, <integerfield>, 0) and
> > you will see that the result is numeric because the query compiler can
> > determine that bot the True and False parts are known to be of integer
> > type.  Note that in SQL, you can also use a 2-argument form of IIf to
> > handle cases where the False part should be null without changing the
> > output column type to string.  If you were to use Null as the False
> > part argument, you would get a string because the query compiler
> > doesn't know the data type of Null.

> > So - back to your UDF.  From the wording of your post, I assume you
> > are returning a variant because the result may be either numeric or
> > Null and those are the only options.  I'm going to assume you're
> > dealing with Integer here, but you can translate the following for
> > whatever type you are actually returning.

> > IIf(<udfcall> Is Not Null, CInt(<udfcall>))

> > This appears inefficient because the UDF may be called twice, but I
> > have found that the optimizer will assume (perhaps incorrectly) that
> > the same UDF call with the same parameter will return the same result
> > and only calls the UDF once, using its return value in both places.
> > You can use debug.print code in your UDF code to test this.

> > On Tue, 10 Apr 2001 00:04:27 +0100, "Neil Sargent"

> > >This is a problem which has always troubled me.

> > >If I declare a public VBA function of type variant and use it in an SQL
> > >statement, Jet always treats the results as a string variant rather
than
> a
> > >numeric data type.
> > >The same is true of the (pseudo) VBA function Nz() - the result is
always
> > >handled as a text field.

> > >    (Oh and by the way, why is Nz() a "pseudo" VBA function in DAO -
> > >sometimes it is recognised like Left(), sometimes it is not -
generating
> a
> > >"Can't use user-defined function in DAO" error)

> > >Here is an example:

> > >Within an SQL statement, I want to add two numeric results fields
> together.
> > >If BOTH fields are Null, I want the result to be Null. If NEITHER field
> is
> > >null, I want the result to be the sum of the two numbers. If either of
> the
> > >fields is null, I want the result to be the non null value.

> > >This is synonomous with the Sum() SQL aggregate function. It returns
Null
> if
> > >all its inputs are Null but returns the sum of any non-null inputs.

> > >I cannot think of a way of achieving the functionality directly from
> SQL/VBA
> > >statements.

> > >If the numeric fields are called TotalSales and TotalForecast, the
> following
> > >occurs

> > >[TotalSales]+[TotalForecast]     returns null if either term is null
> > >Nz([TotalSales])+Nz([TotalForecast])     returns sum as a text field
> > >Nz([TotalSales],0)+Nz([TotalForecast],0)     returns sum as a text
field
> > >(i.e. treats Nz() as a string function even if second parameter is
> numeric)
> > >Val(Nz([TotalSales])+Nz([TotalForecast]))    returns sum as a numeric
> field
> > >but returns 0 if both terms are Null (rather than Null)

> > >To date, I have used the last of these syntaxes, but I have never been
> happy
> > >with it. I want to see a blank (null) if no sales or forecast
> transactions
> > >exist and a zero only if transactions exists but sum to zero. Now I
have
> hit
> > >a requirement which this does not meet.

> > >I have written the following function to attempt to achieve the desired
> > >result:

> > >Function SumNull(ParamArray Val() As Variant) As Variant
> > >' sum a number of values, return Null if all are null, else return sum
> > >    Dim l As Long

> > >    For l = 0 To UBound(Val)
> > >        If Not IsNull(Val(l)) Then
> > >            SumNull = SumNull + Val(l)
> > >        End If
> > >    Next l
> > >    If IsEmpty(SumNull) Then SumNull = Null

> > >End Function

> > >This works fine in report controls but if I use it in the underlying
> query,
> > >the result is a text field (which cannot then be Sum()'d in a report
> footer)

> > >In a report control source

> > >    =SumNull([TotalSales],[TotalForecast])

> > >works OK

> > >In an SQL statement

> > >    SELECT [TotalSales], [TotalForecast],
> > >SumNull([TotalSales],[TotalForecast]) AS TotalVal ...

> > >works but TotalVal is a text field which may not be used in aggregate
> > >controls.

> > >How can I get Jet to do a sum on two or more numeric fields and yield
> Null
> > >only if all terms are Null?

> > >Best Regards

> > >Neil
> > >--------------------------------------------------------
> > >  Neil Sargent
> > >  Smart IT

> > >--------------------------------------------------------



Sat, 27 Sep 2003 22:14:30 GMT  
 Access97: User defined variant function treated as string in query

Quote:

> Effectively I want to say:

> IIf (Val1 AND Val2 AND Val3 Is Not Null, Nz(Val1)+Nz(Val2)+Nz(Val3)) AS
> TotalVal

> However Val1 AND Val2 is Null if either is Null (i.e. if you include a
Null
> in an expression, the result is Null regardless of the operator)

> Any suggestions?

> Best Regards
> Neil

Forgive me if I missed your point, but aren't you effectively saying:

IIf ( (Val1 Is Not Null) OR (Val2 Is Not Null) OR (Val3 Is Not Null)),
Nz.....

May be better way to simplify it?

gary



Tue, 30 Sep 2003 13:46:34 GMT  
 Access97: User defined variant function treated as string in query
Neil,

It only works that way for the arithmetic, comparison, NOT
and XOR operators.

If it makes sense logically, OR, AND and IMP can return a
non-null result.

    (any nonzero value) OR Null  =  CLng(the same nonzero
value)

    0 AND Null  =  0    ' 0 = False

    0 IMP Null  =  -1    ' -1 = True

You can get into trouble with complex If conditions if you
expect Null to propagate across these boolean operators.

Marsh


Quote:
>[snip]
>However Val1 AND Val2 is Null if either is Null (i.e. if
you include a Null
>in an expression, the result is Null regardless of the
operator)
>[snip]



Wed, 01 Oct 2003 00:59:57 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. User-defined Function Procedure in Access Query

2. Answer: User Defined Function in Query called from outside Access

3. executing Access query containing a user-defined function through VB

4. My user-defined functions stopped working in a query

5. Accessing a Microsoft Access Query with a user defined function in VB.

6. HELP: User defined function inside query

7. user defined function in query

8. Executing Access query containing a user-defined function through VB

9. Use of User Defined Function in a query

10. User-defined functions in Queries with Jet?

11. SQL String and User Defined Function

12. User defined type and variant ?

 

 
Powered by phpBB® Forum Software