?
Solved

Query with VBA Function ?

Posted on 2006-05-14
16
Medium Priority
?
400 Views
Last Modified: 2008-02-26
I have query calls a VBA Function. And it works fine, that is from Access. But when I call this query from a VB.NET program which useds this Access Database (and has no problem running other queries) it fails on this Function saying that it does not know this function.  This function does not return anything to VB.NET, it simply runs like a sp would run on SQL Server. Is this the way it is suppose to be - that VB.NET can't such queries in Access? Or is there something that I can do about this?
0
Comment
Question by:szx248
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 16680144
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°)
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 16682565
Which function?
0
 

Author Comment

by:szx248
ID: 16682666
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Accepted Solution

by:
harfang earned 400 total points
ID: 16682943
That's good news, because you don't need VB. Use this in your query:

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

Cheers!
(°v°)
0
 

Author Comment

by:szx248
ID: 16683003
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?
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 16683063
Markus did nail it, because you are using a UDF.
0
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 16683820
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.

0
 

Author Comment

by:szx248
ID: 16683935
LamberHeenan,
sorry I had overlooked Harfang's "-".
Thanks for seeting me straight.
0
 

Author Comment

by:szx248
ID: 16684079
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?
0
 
LVL 58

Expert Comment

by:harfang
ID: 16684308
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°)
0
 

Author Comment

by:szx248
ID: 16684365
>>use the VB function as wrapper

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


0
 
LVL 58

Expert Comment

by:harfang
ID: 16684546
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°)
0
 

Author Comment

by:szx248
ID: 16684609
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.
0
 
LVL 58

Expert Comment

by:harfang
ID: 16685009
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°)
0
 

Author Comment

by:szx248
ID: 16685675
LOL
allmost, but you know what? NZ won't work!
0
 
LVL 58

Expert Comment

by:harfang
ID: 16687860
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)'....
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question