DSUM worked in Access2000, when upsized to SQL 7.0, it fails 3

[wspivak]

I created an Access2000 database with a DSum in a form:

=DSum(" [Commission] + [Metal]+[Diamonds]+[Setting]+[Labor]+[Misc]","Calls","[Calls]![ContactID] =[Contacts].[ContactID]&quot

Worked/Works perfectly.


I then upsized my Access program to SQL. Among the small little problems which cropped up, is that this statement now returns an #Error message.

After some research I found that DSum is not part of the SQL command set.

What's the next best step to take?

 

[Psychpt]

try SUM(........)

 

[wspivak]

Actually tried that, w/o success.

Went and created a view, and put the subform into my major form.

Same idea, not exactly what I wanted, but it works...

 

[tlbroadbent]

DSum does sum of an item in a domain. The item to sum, the domain and criteria are all passed to the function in the call. The equivalent in SQL Server would be...

Select Sum([Commission]+[Metal]+[Diamonds]+[Setting]+[Labor]+[Misc]) As TheSum
From Calls
Where ContactID=<contactid>

You would have to determine the way to provide <criteria> value to the query. What application are you using to build the forms and queries?
Terry

X-) &quot;I don't have a solution, but I admire your problem.&quot;

 

[wspivak]

Curently using MS Access 2000.

My expertise is using MySQL and Web pages on a Roxen Web Server. So Access, while sometimes very easy, and a good proving ground for figuring out SQL statements, is still rather foreign.

 

[tlbroadbent]

If you are linking the SQL tables, the query could be written as

Select Sum([Commission]+[Metal]+[Diamonds]+[Setting]+[Labor]+[Misc]) As TheSum
From Calls
Where ContactID=[forms]![Contacts].[ContactID]

I assume the form name is Contacts and the control name is ContacID.

Terry

X-) &quot;I don't have a solution, but I admire your problem.&quot;