VBA with simple SQL query issue

[Soundje]

Hello There,

I am looking for some help or advice with the following use:

 Dim strQuery As String
strQuery = "SELECT DISTINCTROW tbl_data_leverancier.ContractID, Sum(tbl_data_leverancier.[Hoeveelheid Bruto]) AS [Som Van Hoeveelheid Bruto] FROM tbl_data_leverancier GROUP BY tbl_data_leverancier.ContractID HAVING (((tbl_data_leverancier.ContractID)=[Forms]![frml_contract_lev]![ContractID2]));"
    
 DoCmd.RunSQL strQuery
 Me.som_nettogewicht.Value = strQuery

Can anybody have a look to this I am not able to show the Query result

Many thanks in advanced !!

Kindly Regards,
Koen

 

[vbaInet]

RunSQL is for Action queries like INSERT, DELETE... etc. It's not for a SELECT query.

You can use a DLookup() function for what you want to do.

 

[jdraw]

DoCmd.RunSQL is for action queries Insert, Update , Delete only.

Google this msaccess docmd.runsql

Also see this for SQL aggregate functions


Geeeez: vbaInet is there again.... faster than a speeding bullet....

 

[vbaInet]

Geeeez: vbaInet is there again.... faster than a speeding bullet....

I'm playing catchup. I've not been answering questions all day so I'm trying to get my brain in gear!

 

[jdraw]

I'm playing catchup. I've not been answering questions all day so I'm trying to get my brain in gear!

I've been out all day as well. First post after supper here and bingo --there you are--- quicker than ever.

 

[Soundje]

Thanks a lot vbaInet, I will try this, probarly stuppid question: since I need to calculate the sum of all selected values of 1 colum, is it possible to do this with Dlookup()? if yes could give me a very short example?

Thanks a lot,
Koen

 

[vbaInet]

I've been out all day as well. First post after supper here and bingo --there you are--- quicker than ever.

I did wait a few minutes though so you could have taken advantage

By the way, welcome to the forum Soundje!

 

[vbaInet]

Sure thing Koen, have a look at the following link:

http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[jdraw]

If you are summing 1 column from all records, I think you would use DSum.

Same site as vbaInet suggested, different function

http://www.techonthenet.com/access/functions/domain/dsum.php
He may have another idea in mind.

 

[vbaInet]

I can see that the sum is already being calculated in the query so the OP can use just the DLookup to fetch [Som Van Hoeveelheid Bruto].

 

[Soundje]

Ciao vbaInet,
Once more me sorry for this..

I tried the following code:

Private Sub TEST_Click()
str = DLoopup("SUM[Prijs]", "tbl_data_leverancier", "ContractID = 20141")
MsgBox str

End Sub

But I got a error saying:
Sub of the function is not defined .

Any ideas?

Thanks for all efforts,
Koen

 

[vbaInet]

Did you save the SQL statement you had in your first post as a query?

 

[Soundje]

Yes I have the nam of the query is
que_som_data_leverancier

I am getting a littlebit confused sorry.. probarly due to the time

Thanks
Koen

 

[vbaInet]

Time to sleep in Belgium and here too

What's the name of the query? And the SQL statement in your first post is the same as what's in the query, nothing has changed?

 

[Soundje]

I found it thanks to a cup of coffee

str = DLookup("[Som Van Hoeveelheid Bruto]", "que_som_data_leverancier", "ContractID = 20141")

right?

 

[vbaInet]

Yes, good job! But you know the criteria should be coming from your form right?

 

[Soundje]

Thanks a lot for all help vbaInet, i couldn't make it without your triggers and help !!!
Really appriciated it !!!!

Have a nice evening/night,
Koen

 

[vbaInet]

Glad we could help!