Domain aggregate function

Question

Post reply

Domain aggregate function

kiberu

SSC-Addicted

Points: 496
More actions
July 18, 2003 at 7:20 am

#82287

Kindly show me the equivalent of MSAccess Dsum() function

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi kiberu,

quote:
Kindly show me the equivalent of MSAccess Dsum() function

given the Access Online Help example

Dim curX As Currency

curX = DSum("[Frachtkosten]", "Bestellungen", "[Bestimmungsland] = 'UK'")

I guess the equivalent would be

DECLARE @curX decimal(8,2)

SELECT @curX = SUM(Frachtkosten) FROM Bestellungen WHERE Bestimmungsland = 'UK'

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

NPeeters SSChampion Points: 12612 More actions · Answer 2

If I understand the meaning of DSUM correctly, you should use a GROUP BY clause.

With



DSum(expr, domain, criteria)

, the query would look like



SELECT SUM(expr)

FROM <your table>

WHERE domain = criteria

GROUP BY domain

kiberu SSC-Addicted Points: 496 More actions · Answer 3

Hi, thanks, but this is from a VBA code behind a form where the computation is done:

Me.TotValue = DSum("Revenue", "QryOrderFooter")and qryOrderFooter is a stored procedure.

kiberu SSC-Addicted Points: 496 More actions · Answer 4

Hi, thanks, but this is from a VBA code behind a form where the computation is done:

Me.TotValue = DSum("Revenue", "QryOrderFooter")and qryOrderFooter is a stored procedure.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hi Noel,

quote:
If I understand the meaning of DSUM correctly, you should use a GROUP BY clause.
With

DSum(expr, domain, criteria)
, the query would look like

SELECT SUM(expr)
FROM <your table>
WHERE domain = criteria
GROUP BY domain

no, with domain is not meant the SQL92 or 99 ? feature

Look at when you use DSUM!

It is not used in 'normal' Access queries, Access uses there also the SUM() function. You use DSUM only from within a macro, query expression ?!? (in german Abfrageausdruck) or, I guess, most common in a calculated control on an Access form.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

quote:
Hi, thanks, but this is from a VBA code behind a form where the computation is done:
Me.TotValue = DSum("Revenue", "QryOrderFooter")and qryOrderFooter is a stored procedure.

can you post qryOrderFooter?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Just another thought.

Are you moving from Access to SQL Server?

Will Access be the Front-end or will this change?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

kiberu SSC-Addicted Points: 496 More actions · Answer 8

Correct, Moved to ACCESS ADP and my forms have gone beserk!

FE is in Access 2K (Office 2K SP3)

kiberu SSC-Addicted Points: 496 More actions · Answer 9

Thanks collegues,

Here is QryOrderfooter : purpose to compute the value of the items on the order form.

Alter Procedure QryOrderFooter

(

@OrderId int OUTPUT

)

As

SELECT OrderId,QTY,QtyValue, (QTY * QtyValue) as Revenue

FROM qryOrder

WHERE (qryOrder.OrderId = @OrderId);

return

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

quote:
Correct, Moved to ACCESS ADP and my forms have gone beserk!
FE is in Access 2K (Office 2K SP3)

What happens?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

kiberu SSC-Addicted Points: 496 More actions · Answer 11

Error message, that Domain aggregate functions not allowed in VBA?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 12

quote:
Error message, that Domain aggregate functions not allowed in VBA?

I've tested this one

Me.Text2 = DSum("[id]", "[dbo_mails_header]", "[id]<1000")

works fine.

Why not change sp to

SELECT OrderId,QTY,QtyValue, (QTY * QtyValue) as Revenue FROM qryOrder

and do the rest from within Access?

Is qryOrder another query or a table?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

NPeeters SSChampion Points: 12612 More actions · Answer 13

Frank, thanks for the explaining, I'll use the smiley on myself this time ...

Frank Kalis SSC Guru Points: 111183 More actions · Answer 14

Hi kiberu,

quote:
Alter Procedure QryOrderFooter
(
@OrderId int OUTPUT
)
As
SELECT OrderId,QTY,QtyValue, (QTY * QtyValue) as Revenue
FROM qryOrder
WHERE (qryOrder.OrderId = @OrderId);

just for explanation.

The domain aggregate functions in Access are pretty similar to 'normal' SQL statements without the SQL keywords. In addition you don't have to explicitely open recordsets and this things. Meaning at first sight you save some lines of code

Dim curX As Currency

curX = DSum("[Frachtkosten]", "Bestellungen", "[Bestimmungsland] = 'UK'")

I'm not sure, if you can run stored procedures within these functions, especially such a 'parameterized' one like yours.

The third parameter in DSUM is for such a parameter. It lets you specify search criteria like '"[Bestimmungsland}= 'UK'"'.

You are also able to do this dynamically

Dim lpszSearchString as String

lpszSearchString="[Bestimmungsland}= 'UK' AND [OrderID]=" & Me.OrderID & """

Put this as third parameter and it should work!

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]