Dsum returns #Error

[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.

 

[Marshall Barton]

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.

If you can live with Sum, it is preferred over DSum.

The usual problem with using that kind of function is when
you are concatenating a field's value to something and the
field contains Null. For example:
DSum("fieldA","table", "fieldB=" & fieldB)
will get a syntax error on any record where fieldB is Null.

If you'll post a Copy/Paste of your query, maybe someone can
spot potential problems.

 

[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.

 

[Marshall Barton]

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.

 

[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.

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.

 

[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..

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.

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.

 

[Marshall Barton]

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..

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.

 

[lwidjaya]

u r absolutely correct.

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..

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.

.