Sum in Report field

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

=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

 

[John Spencer]

You posted

=Sum(Iif(

=1, [amount], 0)

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

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

 

[Welby]

Is indeed a typo or rather a 'copypasto'

Welby

You posted

=Sum(Iif(

=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]

 

[John Spencer]

I don't see any reason that this would fail. Anyone else?

Is indeed a typo or rather a 'copypasto'

Welby

You posted

=Sum(Iif(

=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]

 

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

I don't see any reason that this would fail. Anyone else?

Is indeed a typo or rather a 'copypasto'

Welby

You posted

=Sum(Iif(

=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]

 

[Duane Hookom]

I'm surprise the IIf() was pasted in as Iif().
This should work if your references check out ok:
=Sum(IIf(

=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]