missing operator error 1

[burnside]

why do i get this error-

syntax error(missing operator)in query exprssion
'DSum ([lots].price sold)'

in this sql

SELECT customers.cusID, customers.[customer name], customers.cash, customers.cheque, customers.switch, customers.card, DSum([lots].price sold) AS lottot
FROM customers INNER JOIN lots ON customers.cusID = lots.cusID
WHERE (((customers.cusID)=[forms]![purchinvselect]![purchname]));

 

[Golom]

The format for DSum is

DSum(expr, domain, [criteria])

You have not specified the domain (i.e. Table Name) in your use of DSum.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Pampers]

price sold--> [price sold]?

Pampers
Just let it go...

 

[JerryKlmns]

A wild guess

DSum("[price sold]", "lots", "lots.cusID=" & [forms]![purchinvselect]![purchname])

 

[burnside]

many thanks
used this
DSum("[price sold]","lots") AS lottot

 

[burnside]

errr.... this sql is totalling All lots.price sold in the table and i dont know why

SELECT customers.cusID, customers.[customer name], customers.cash, customers.cheque, customers.switch, customers.card, DSum("[price sold]","lots") AS lottot
FROM customers INNER JOIN lots ON customers.cusID = lots.cusID
WHERE (((customers.cusID)=[forms]![purchinvselect]![purchname]));

 

[Golom]

You haven't supplied any criteria for the DSum. Look at JerryKlmns's post.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[burnside]

thanks - but wht do i need that as well as the WHERE
part on the sql

cheers MG

 

[Golom]

Because the DSum command is executed independently of the rest of the SQL statement. It is just a function that returns a value based on its calling arguments and doesn't know anything else about the context from which it was called.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[burnside]

many thanks

 

[PHV]

Why using the slow DSum function ?
Doesn't this meet your requirment ?
SELECT C.cusID, C.[customer name], C.cash, C.cheque, C.switch, C.card, Sum(L.[price sold]) AS lottot
FROM customers AS C INNER JOIN lots AS L ON C.cusID = L.cusID
WHERE C.cusID = [Forms]![purchinvselect]![purchname]
GROUP BY C.cusID, C.[customer name], C.cash, C.cheque, C.switch, C.card

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886