Query with VBA Function ?

Hello szx248

Jet uses the VB function library for most functions and operators, so you can use all built-in VB functions in a Jet query, regardless of the context.

However, user-defined functions are not available as such. They are only when Jet is run from the proper VBA context, i.e. Access. This is why you cannot use them in validation rules, for example.

(°v°)

Which function?

This is the fucntion:


Public Function DeliveryTotalCases(ByVal DeliveryHeaderID As Long) As Long
Dim TotalCases As Long
TotalCases = Nz(DSum("Cases", "qryDeliveryDetail_LotDetail", "DeliveryHeaderID=" & DeliveryHeaderID), 0)

If TotalCases < 0 Then
    DeliveryTotalCases = Abs(TotalCases)
ElseIf TotalCases > 0 Then
    DeliveryTotalCases = TotalCases * -1
Else
    DeliveryTotalCases = TotalCases
End If

End Function


I am assuming that Harfaq's answer is correct, and I was going to close this. But from your qestion it seems that there may be more to it.

Well no quite. In fact the Dsum will work (at I would infer this from your first comment). But I wanted to avoice having to run it more than once. Cause without being able to assign to var how can I do the next few lines (to change + to - and - to +) without calling this Sum() again?

Markus did nail it, because you are using a UDF.

Leaving aside the fact that Dsum() is inherantly slow and so should be avoided in queres if possible, the logic in your UDF that checks the sign of the result is...

If TotalCases < 0 Then
    DeliveryTotalCases = Abs(TotalCases)
ElseIf TotalCases > 0 Then
    DeliveryTotalCases = TotalCases * -1
Else
    DeliveryTotalCases = TotalCases
End If

.. which translates to " if it's negative return a posive value, if it's positive return a negative value, otherwise return the value" As te "otherwize" case can only be for the value Zero, all you need to do is as harfang says, the minus sign in from of Nz does the sing conversion for you. negative turn positve an vise versa, and zero is of course not changed.

In fact your entire function could be rewritten as...

Public Function DeliveryTotalCases(ByVal DeliveryHeaderID As Long) As Long
    DeliveryTotalCases=  _  
          Nz(DSum("Cases", "qryDeliveryDetail_LotDetail", "DeliveryHeaderID=" & DeliveryHeaderID), 0) * -1
End Function

but it would still be slow because of the use of Dsum.

LamberHeenan,
sorry I had overlooked Harfang's "-".
Thanks for seeting me straight.

btw, when I try using

-Nz(( Select Sum(Cases) From qryDeliveryDetail_LotDetail Where DeliveryHeaderID= <parent table/query name>.DeliveryHeaderID), 0)

or

 Select -Sum(nz(Cases,0)) From qryDeliveryDetail_LotDetail Where DeliveryHeaderID= <parent table/query name>.DeliveryHeaderID

in the Update To row of the Update Query I get an error that says Operation must use an Updateable Query.

However this works -
DSum("-nz(Cases,0)","qryDeliveryDetail_LotDetail","DeliveryHeaderID=" & [tblDeliveryHeader].[ID])

I certainly don't prefer dsum() but why is the select not working?

Because the subquery (a real query in brackets) gets optimized by the engine to run much faster. This involves creating implicit relationships from the WHERE clauses, thus linking an updatable query with a non-updatable total query (the Sum()).

If you need to update (but why?), use the VB function as wrapper. Your query does not get optimized, but it remains updatable. The only other solution in Access is to use an intermediate temp table containing the sums (you can use a non-updatable query for a make table query, but not for an update query). Too much hassle here it seems.

Cheers!
(°v°)

>>use the VB function as wrapper

But this won't work when the query is called from vb.net, right?

I suppose not. DSum() is an Access function, not VB/Jet, so it probably isn't available.

If you need the temp table, simply run:

    SELECT DeliveryHeaderID, Sum(Cases) As SumCases
    INTO zttblDeliveryTotals

Then use zttblDeliveryTotals in your update query. Most large database systems make extensive use of temp tables. No shame there.

(°v°)

no no. dsum() DOES work from vb.net.
But you suggestion to use subquery by putting it into a vba code will not run.

temp tables are great when you can use them in sp like in ms sql but using them in code is a big mess, plus u have to cleam them up which you don't have to do in sql server.

Great, if DSum() works, you are set. What I meant with "wrapping function" was DSum(), although that is not  entirely true, not a user-defined function.

So this is sorted out?
Good luck!
(°v°)

LOL
allmost, but you know what? NZ won't work!

Hmmm.... you get an error, or just not the expected result? You should have the Nz outside, like in: Nz(DSum('cases'.... not DSum('nz(cases)'....