Use parameter value to evaluate data

[nody88]

Hello,
I am facing a following issue.

I have query with a parameter [Category]. Based on this value i wish to select team data like if [Category]="Sale" then (team) in (1,9,5), if [Category]="Purchase" then (team)="7" else (team)
in (1,9,5,7).
Both the fields team and category are from same table called 'rawdata'

Report displays the data datewise but it is grouping according to team,which is not what i required;
Date CntValues
12/1/2013 1 [team 1]
12/1/2013 3 [team 9]

output should be
12/1/2013 4

that is to avoid duplicate date values.

Please help.

 

[namliam]

That is becuase your TEAM column as "GROUP BY" to it instead of "WHERE", change that and you win

p.s. welcome to AWF

 

[nody88]

in where criteria

IIf([Category]="Sales",[rawdata]![team] In ("1","5","9"),[rawdata]![team]="7")

giving message as
"expression is typed incorrectly or too complex to evaluate"

 

[nanscombe]

If you are using the Query builder it sounds like you want to create three sets of criteria each on it's own line.

Field		Category				Team
Criteria	"Sales"				In(1,9,5)
Criteria	"Purchase"	 		7
Criteria	Not In("Sales", "Purchase")	In(1,9,5,7)

The fields in a single line of criteria are joined with AND statements, the separate lines of a criteria are joined with an OR statement, so you would end up with a clause which says ...

WHERE ((Category = "Sales") AND (Team In (1,9,5))
OR ((Category = "Purchase") AND (Team = 7))
OR ((Category Not In ("Sales, "Purchase") AND (Team In (1,9,5,7))

 

[nody88]

What if for "Purchase" criteria i want to select all the teams which has criteria as 'purchase'?

As if i dont want to hard code team values for 'purchase'.

 

[nanscombe]

Do you mean removing the team values for Category = "purchase"?

WHERE ((Category = "Sales") AND (Team In (1,9,5))
OR ((Category = "Purchase") AND [COLOR="Red"](Team = 7)[/COLOR])
OR ((Category Not In ("Sales, "Purchase") AND (Team In (1,9,5,7))

WHERE ((Category = "Sales") AND (Team In (1,9,5))
OR (Category = "Purchase")
OR ((Category Not In ("Sales, "Purchase") AND (Team In (1,9,5,7))