Help with "IIF"

[Zilla]

Hi,

I have a control on a report which looks like this:

=IIf([Gender]="m" And [age]="4",Sum(Abs([Age])),"0")

....but Access gives me an error msg telling me that this is either typed
incorrectly or is too complicated to be evaluated. Can anyone help?! Thanks
in advance.

 

[Dennis]

is age text or numeric ?
You are testing for a text age 4 because you have enclosed it in quotes, but
are then trying to do maths on it with your sum.
Your expression needs to be either

=IIf([Gender]="m" And [age]="4",Sum(Abs(Val([Age]))),0)

or

=IIf([Gender]="m" And [age]=4,Sum(Abs([Age])),0)

 

[fredg]

Hi,

I have a control on a report which looks like this:

=IIf([Gender]="m" And [age]="4",Sum(Abs([Age])),"0")

...but Access gives me an error msg telling me that this is either typed
incorrectly or is too complicated to be evaluated. Can anyone help?! Thanks
in advance.

Do you wish to add up all of the ages for those males of the age of 4?
i.e. 3 m's of Age 4 you wish to show the number 12?

Or did you want to count how many m's of 4 years of age there were,
i.3. show 3?

Is [Age] a Text of Number datatype"?
It should be a number calculated in the Report's Record Source at the
time the report is run, using a stored [DateOfBirth] field to compute
it.
In a query:
Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

You do know, I hope, that this Age computation should NOT be stored in
any table.
Just compute it and display it on a form or report, as needed.

Then, to sum their ages in your report, use:
=Sum(IIf([Gender]="m" and [Age] = 4,[Age],0))

To count how many there are of that age, use:
=Sum(IIf([Gender]="m" and [Age] = 4,1,0))