Sum in Report field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

A report developed - many years ago - in a Access 97 database, has as record
source a query called qrySalesResults. This query contains several fields
among others the fields code and amount. One of the textboxes in the report
contains this expression:
=Sum(Iif(
Code: 
=1, [amount], 0)
it worked perfectly under Access 97. It gets me the total of amount, of
those records in de recordsource where code is 1.

After conversion to Access 2002, this field generates an error:
3071 This expression is typed incorrectly, or it is too complex to be
evaluated. ...

I tried to replace the expression with this one:
DSum("[amount]", "qrySalesResults", "[code]=1")
I have to cancel the operation because no report showing even after 10
minutes.

So how to go from here?

Thanks
 
You posted

=Sum(Iif(
Code: 
=1, [amount], 0)

Is that a typo - there is a missing ")" ?

=Sum(Iif([code]=1, [amount], 0))
 
Is indeed a typo or rather a 'copypasto'

Welby

John Spencer said:
You posted

=Sum(Iif(
Code: 
=1, [amount], 0)

Is that a typo - there is a missing ")" ?

=Sum(Iif([code]=1, [amount], 0))


[QUOTE="Welby"]
A report developed - many years ago - in a Access 97 database, has as
record
source a query called qrySalesResults. This query contains several fields
among others the fields code and amount. One of the textboxes in the
report
contains this expression:
=Sum(Iif([code]=1, [amount], 0)
it worked perfectly under Access 97. It gets me the total of amount, of
those records in de recordsource where code is 1.

After conversion to Access 2002, this field generates an error:
3071 This expression is typed incorrectly, or it is too complex to be
evaluated. ...

I tried to replace the expression with this one:
DSum("[amount]", "qrySalesResults", "[code]=1")
I have to cancel the operation because no report showing even after 10
minutes.

So how to go from here?

Thanks[/QUOTE]
[/QUOTE]
Click to expand...
 
I don't see any reason that this would fail. Anyone else?
Welby said:
Is indeed a typo or rather a 'copypasto'

Welby

John Spencer said:
You posted

=Sum(Iif(
Code: 
=1, [amount], 0)

Is that a typo - there is a missing ")" ?

=Sum(Iif([code]=1, [amount], 0))


[QUOTE="Welby"]
A report developed - many years ago - in a Access 97 database, has as
record
source a query called qrySalesResults. This query contains several
fields
among others the fields code and amount. One of the textboxes in the
report
contains this expression:
=Sum(Iif([code]=1, [amount], 0)
it worked perfectly under Access 97. It gets me the total of amount, of
those records in de recordsource where code is 1.

After conversion to Access 2002, this field generates an error:
3071 This expression is typed incorrectly, or it is too complex to be
evaluated. ...

I tried to replace the expression with this one:
DSum("[amount]", "qrySalesResults", "[code]=1")
I have to cancel the operation because no report showing even after 10
minutes.

So how to go from here?

Thanks[/QUOTE]
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
Just curious is the name of the field 'code'? Never a good idea.

But in any event, you might want to check your references.


John Spencer said:
I don't see any reason that this would fail. Anyone else?
Welby said:
Is indeed a typo or rather a 'copypasto'

Welby

John Spencer said:
You posted

=Sum(Iif(
Code: 
=1, [amount], 0)

Is that a typo - there is a missing ")" ?

=Sum(Iif([code]=1, [amount], 0))


A report developed - many years ago - in a Access 97 database, has as
record
source a query called qrySalesResults. This query contains several
fields
among others the fields code and amount. One of the textboxes in the
report
contains this expression:
=Sum(Iif([code]=1, [amount], 0)
it worked perfectly under Access 97. It gets me the total of amount,
of
those records in de recordsource where code is 1.

After conversion to Access 2002, this field generates an error:
3071 This expression is typed incorrectly, or it is too complex to be
evaluated. ...

I tried to replace the expression with this one:
DSum("[amount]", "qrySalesResults", "[code]=1")
I have to cancel the operation because no report showing even after 10
minutes.

So how to go from here?

Thanks
[/QUOTE][/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
I'm surprise the IIf() was pasted in as Iif().
This should work if your references check out ok:
=Sum(IIf(
Code: 
=1, [amount], 0))
Another option is:
=Sum(Abs([code]=1)*[amount])

Is the Code field truly numeric?

--
Duane Hookom
MS Access MVP
--

[QUOTE="Gina Whipp"]
Just curious is the name of the field 'code'?  Never a good idea.

But in any event, you might want to check your references.


[QUOTE="John Spencer"]
I don't see any reason that this would fail.  Anyone else?
[QUOTE="Welby"]
Is indeed a typo or rather a 'copypasto'

Welby

:

You posted

=Sum(Iif([code]=1, [amount], 0)

Is that a typo - there is a missing ")" ?

=Sum(Iif([code]=1, [amount], 0))


A report developed - many years ago - in a Access 97 database, has as
record
source a query called qrySalesResults. This query contains several
fields
among others the fields code and amount. One of the textboxes in the
report
contains this expression:
=Sum(Iif([code]=1, [amount], 0)
it worked perfectly under Access 97. It gets me the total of amount,
of
those records in de recordsource where code is 1.

After conversion to Access 2002, this field generates an error:
3071 This expression is typed incorrectly, or it is too complex to be
evaluated. ...

I tried to replace the expression with this one:
DSum("[amount]", "qrySalesResults", "[code]=1")
I have to cancel the operation because no report showing even after
10
minutes.

So how to go from here?

Thanks
[/QUOTE]
[/QUOTE]
[/QUOTE]
 
Back
Top