Dsum returns #Error

L

lwidjaya

Hi.
I created a report based on a query that uses Dsum function. This query runs
well in the server where I created it. But when I ran it in a different PC,
it gave me #error on the query result on the field that uses DSum. I compared
reference files used on both server and the PC and they were the same. Is
there any specific dll I need to add to the PC to make it work? I have to
modify it to sum(iif()) to make it work now.

Thanks.
 
L

lwidjaya

This is my query:
SELECT DatePart("yyyy",[Date]) AS AYear, DatePart("m",[Date]) AS AMonth,
Sum(IIf([EMP]='FRANCE, SALES' And DatePart('m',[Date])=[AMonth] And
DatePart('yyyy',[Date])=[AYear],[SplitAmount],0)) AS FR, Sum(IIf([EMP]='ABC,
IN' And DatePart('m',[Date])=[AMonth] And
DatePart('yyyy',[Date])=[AYear],[SplitAmount],0)) AS IN, Sum(IIf([EMP]='AR,
SALES' And DatePart('m',[Date])=[AMonth] And
DatePart('yyyy',[Date])=[AYear],[SplitAmount],0)) AS AR, Format([Date],"mmm")
AS FDate, Sum(IIf(DatePart('m',[Date])=[AMonth] And
DatePart('yyyy',[Date])=[AYear],[SplitAmount],0)) AS Total INTO
tblSalesTallySummary1
FROM tblSalesTally1
GROUP BY DatePart("yyyy",[Date]), DatePart("m",[Date]), Format([Date],"mmm");
It doesn't make sense for me because it works in one computer but not on
another?

The reason I used DSUM was because there are some conditions involved.

Thanks.
 
L

lwidjaya

I checked references on both computers and they match. The reason I didn't
use grouping in the report was because of the way users want to see the data.
This query will create a table which will be part of a comparison query. The
report will be like this:

2009
2010
FR IN AR
Total FR IN AR
Total
Jan 985 115 7
1107 1000 77 11 1088
Feb 1000 81 17
1098 1050 37 15 1102
March
April
so on...

Finally I replaced dsum with iif(sum()) and it worked on both computers now.
Thanks.

Marshall Barton said:
lwidjaya said:
This is my query:
SELECT DatePart("yyyy",[Date]) AS AYear,
DatePart("m",[Date]) AS AMonth,
Sum(IIf([EMP]='FRANCE, SALES' And DatePart('m',[Date])=[AMonth]
And DatePart('yyyy',[Date])=[AYear],[SplitAmount],0)) AS FR,
Sum(IIf([EMP]='ABC, IN' And DatePart('m',[Date])=[AMonth]
And DatePart('yyyy',[Date])=[AYear],[SplitAmount],0)) AS IN,
Sum(IIf([EMP]='AR, SALES' And DatePart('m',[Date])=[AMonth]
And DatePart('yyyy',[Date])=[AYear],[SplitAmount],0)) AS AR,
Format([Date],"mmm") AS FDate,
Sum(IIf(DatePart('m',[Date])=[AMonth]
And DatePart('yyyy',[Date])=[AYear],[SplitAmount],0)) AS Total
INTO tblSalesTallySummary1
FROM tblSalesTally1
GROUP BY DatePart("yyyy",[Date]),
DatePart("m",[Date]),
Format([Date],"mmm")

It doesn't make sense for me because it works in one computer but not on
another?

The reason I used DSUM was because there are some conditions involved.
Click to expand...

Even with all those conditions, I still prefer Sum over
DSum, especially if you should ever add a Where clause to
the query.

After making it more readable, I still don't see anything
that would cause a problem on one machine. I don't see
anything where different table contents would glitch things
up either. Have you verified that all the references are ok
on the problem machine? Does everything else work on both
machines?

As little as that is, that's the best I can do about your
specific question.

BUT, you said this query is to be used as a report's record
source, right? If so, I do not understand why you are doing
all that in the query. If the report is grouping on the EMP
field (and the month year combination), I would think it
would be a LOT easier to use a plain, ordinary Sum
expression in the report/group header/footer sections.
Without all that glop, it would be a very simple query and a
pretty straightforward report.
Click to expand...
 
L

lwidjaya

i hope this one looks better:

2009
2010

FR IN
AR Total FR IN AR
Total
Jan 985 115
7 1107 1000 77 11
1088
Feb 1000 81
17 1098 1050 37 15
1102
March
April
so on..

lwidjaya said:
I checked references on both computers and they match. The reason I didn't
use grouping in the report was because of the way users want to see the data.
This query will create a table which will be part of a comparison query. The
report will be like this:

2009
2010
FR IN AR
Total FR IN AR
Total
Jan 985 115 7
1107 1000 77 11 1088
Feb 1000 81 17
1098 1050 37 15 1102
March
April
so on...

Finally I replaced dsum with iif(sum()) and it worked on both computers now.
Thanks.

Marshall Barton said:
lwidjaya said:
This is my query:
SELECT DatePart("yyyy",[Date]) AS AYear,
DatePart("m",[Date]) AS AMonth,
Sum(IIf([EMP]='FRANCE, SALES' And DatePart('m',[Date])=[AMonth]
And DatePart('yyyy',[Date])=[AYear],[SplitAmount],0)) AS FR,
Sum(IIf([EMP]='ABC, IN' And DatePart('m',[Date])=[AMonth]
And DatePart('yyyy',[Date])=[AYear],[SplitAmount],0)) AS IN,
Sum(IIf([EMP]='AR, SALES' And DatePart('m',[Date])=[AMonth]
And DatePart('yyyy',[Date])=[AYear],[SplitAmount],0)) AS AR,
Format([Date],"mmm") AS FDate,
Sum(IIf(DatePart('m',[Date])=[AMonth]
And DatePart('yyyy',[Date])=[AYear],[SplitAmount],0)) AS Total
INTO tblSalesTallySummary1
FROM tblSalesTally1
GROUP BY DatePart("yyyy",[Date]),
DatePart("m",[Date]),
Format([Date],"mmm")

It doesn't make sense for me because it works in one computer but not on
another?

The reason I used DSUM was because there are some conditions involved.
Click to expand...

Even with all those conditions, I still prefer Sum over
DSum, especially if you should ever add a Where clause to
the query.

After making it more readable, I still don't see anything
that would cause a problem on one machine. I don't see
anything where different table contents would glitch things
up either. Have you verified that all the references are ok
on the problem machine? Does everything else work on both
machines?

As little as that is, that's the best I can do about your
specific question.

BUT, you said this query is to be used as a report's record
source, right? If so, I do not understand why you are doing
all that in the query. If the report is grouping on the EMP
field (and the month year combination), I would think it
would be a LOT easier to use a plain, ordinary Sum
expression in the report/group header/footer sections.
Without all that glop, it would be a very simple query and a
pretty straightforward report.
Click to expand...
Click to expand...
 
L

lwidjaya

:D u r absolutely correct.


Marshall Barton said:
No, it doesn't look any better ;-(

But if you got it working the way you want, it doesn't
really matter ;-)
--
Marsh
MVP [MS Access]

i hope this one looks better:

2009
2010

FR IN
AR Total FR IN AR
Total
Jan 985 115
7 1107 1000 77 11
1088
Feb 1000 81
17 1098 1050 37 15
1102
March
April
so on..

lwidjaya said:
I checked references on both computers and they match. The reason I didn't
use grouping in the report was because of the way users want to see the data.
This query will create a table which will be part of a comparison query. The
report will be like this:

2009
2010
FR IN AR
Total FR IN AR
Total
Jan 985 115 7
1107 1000 77 11 1088
Feb 1000 81 17
1098 1050 37 15 1102
March
April
so on...

Finally I replaced dsum with iif(sum()) and it worked on both computers now.
Thanks.

:

lwidjaya wrote:
This is my query:
SELECT DatePart("yyyy",[Date]) AS AYear,
DatePart("m",[Date]) AS AMonth,
Sum(IIf([EMP]='FRANCE, SALES' And DatePart('m',[Date])=[AMonth]
And DatePart('yyyy',[Date])=[AYear],[SplitAmount],0)) AS FR,
Sum(IIf([EMP]='ABC, IN' And DatePart('m',[Date])=[AMonth]
And DatePart('yyyy',[Date])=[AYear],[SplitAmount],0)) AS IN,
Sum(IIf([EMP]='AR, SALES' And DatePart('m',[Date])=[AMonth]
And DatePart('yyyy',[Date])=[AYear],[SplitAmount],0)) AS AR,
Format([Date],"mmm") AS FDate,
Sum(IIf(DatePart('m',[Date])=[AMonth]
And DatePart('yyyy',[Date])=[AYear],[SplitAmount],0)) AS Total
INTO tblSalesTallySummary1
FROM tblSalesTally1
GROUP BY DatePart("yyyy",[Date]),
DatePart("m",[Date]),
Format([Date],"mmm")

It doesn't make sense for me because it works in one computer but not on
another?

The reason I used DSUM was because there are some conditions involved.

Even with all those conditions, I still prefer Sum over
DSum, especially if you should ever add a Where clause to
the query.

After making it more readable, I still don't see anything
that would cause a problem on one machine. I don't see
anything where different table contents would glitch things
up either. Have you verified that all the references are ok
on the problem machine? Does everything else work on both
machines?

As little as that is, that's the best I can do about your
specific question.

BUT, you said this query is to be used as a report's record
source, right? If so, I do not understand why you are doing
all that in the query. If the report is grouping on the EMP
field (and the month year combination), I would think it
would be a LOT easier to use a plain, ordinary Sum
expression in the report/group header/footer sections.
Without all that glop, it would be a very simple query and a
pretty straightforward report.
Click to expand...
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

dsum sytax error 5
DSum doesn't work on other PCs 1
dsum with multiple criteria and decimals 0
Parameter Query with DSum 1
Dsum Problem with date criteria 1
dsum 3
DSUM Error 3
Dsum Problem 3

Top