Grouping by Age

I have a table Age and need to create Report by Grouping Salesfigures according to Age.
I put the following expression into Grouping and Sorting Properties/General/Filter /Sorting Expression... as well as in Textbox Properties/ values..

=IIF(Fields!Age.Value < 16, "<16",IIF(Fields!Age.Value <21,"16-20")

Errormessage: Value expression for textbox "Age" error: Argument not specified for parameter 'FalsePart' of 'Public function IIF(Expression as Boolean, TruePart as Object, Falsepart As Object) As Object'.

Question 2 .
How to return Month as January, February... In correct order?
"DATENAME(mm, Sales.time) AS Month" (Ascending ) returns starting with April, August...
DATEPART(mm, Sales.time) AS Month (Ascending ) returns starting with 1, 10, 11 ...

Answer 1

Your expression is incomplete. As the error message says, you are missing the FlasePart of the nested Iif function, as well a parentheses

=IIF(Fields!Age.Value < 16, "<16",IIF(Fields!Age.Value <21,"16-20", ">20" ) )

Can you clarify Quaestion 2. E.g. where are you putting this code, can you paste your query?

|||

For Question 1 you don't have a false path to follow in your second iif.

=IIF(Fields!Age.Value < 16, "<16",IIF(Fields!Age.Value <21,"16-20",""))

|||Thank you so much!

SELECT SUM(Cd.Price) AS Sales, DATENAME(mm, Purchase.time) AS Month, Staff.Name
FROM Staff INNER JOIN
Purchase ON Staff.Staff_id = Purchase.Salesperson_id INNER JOIN
Cd ON Purchase.Cd_id = Cd.Cd_id
GROUP BY DATENAME(mm, Purchase.time), Staff.Name
ORDER BY Month Asc

The outcome is starting with April...
(not with January)?|||Thank you.. still, "Argument not specified for false part". Something I've misunderstood?

=IIF(Fields!Age.Value < 16, "<16",IIF(Fields!Age.Value <21,"16-20"),IIF(Fields!Age.Value <31,"21-30"), IIF(Fields!Age.Value < 41,"31-40"),IIF(Fields!Age.Value <51,"41-50",">50"))

I put this value expression In textbox "Age"/Expression, as well as in Grouping Sorting properties /General/Filter/ Sorting|||

Yes you have misunderstood this slightly. The definition of the Iif function is

Iif(<<condition>>, TruePart, FalsePart)

Your expression puts many Iif's all passed into a single Iif. You actually have to nest the Iif's as the FalsePart of the previous Iif

Your expression:
=IIF
( Fields!Age.Value < 16 <- condition
, "<16" <- TruePart
, IIF(Fields!Age.Value <21,"16-20") <- FalsePart nested IIF
, IIF(Fields!Age.Value <31,"21-30") <- Error 4th argument
, IIF(Fields!Age.Value <41,"31-40") <- Error 5th argument
, IIF(Fields!Age.Value <51,"41-50",">50") <- Error 6th argument
)

Correct Expression:
=IIF
( Fields!Age.Value < 16 <- condition
, "<16" <- TruePart
, IIF <- FalsePart nested IIF
( Fields!Age.Value < 21 <- condition
,"16-20" <- TruePart
, IIF <- FalsePart nested IIF
( Fields!Age.Value < 31 <- condition
, "21-30" <- TruePart
, IIF <- FalsePart nested IIF
( Fields!Age.Value < 41 <- condition
, "31-40" <- TruePart
, IIF <- FalsePart nested IIF
( Fields!Age.Value < 51 <- condition
, "41-50" <- TruePart
, ">50" <- FalsePart
)
)
)
)
)

just make sure you get the parentheses right and you remove the comments

|||

As far as your query goes, display the name but order by number. for this to work you must include both in the GROUP BY clause

SELECT SUM(Cd.Price) AS Sales, DATENAME(mm, Purchase.time) AS Month, Staff.Name
FROM Staff INNER JOIN
Purchase ON Staff.Staff_id = Purchase.Salesperson_id INNER JOIN
Cd ON Purchase.Cd_id = Cd.Cd_id
GROUP BY DATENAME(mm, Purchase.time)
, DATEPART(mm, Purchase.time)
, Staff.Name
ORDER BY DATEPART(mm, Purchase.time) Asc

|||

Would it be possible to create a field from a select e.g.

SELECT Age, AgeGroup =

CASE

WHEN (age >= 1 and age <= 3) THEN 'Age 1-3'

WHEN (age >= 4 and age <= 5) THEN 'Age 4-5'

WHEN (age >= 6 and age <= 7) THEN 'Age 6-7'

ELSE 'Over age'

END

FROM tblAge

|||In fact, I prefer this option, give RS less work to do.