Combine 2 criteria into a single criteria to show win/loss
- Thread starter cookitup
- Start date
CraigDolphin
GrumpyOldMan in Training
- Local time
- Today, 06:06
- Joined
- Dec 21, 2005
- Messages
- 1,578
If I understand you correctly, you have a field that currently contains three possible values: Active, Closed, and Have Pricing.
You want to display the word "Won" if the value is either Active or Closed, and the word "Lost" if the value is Have Pricing?
If that's correct, then you would use a calculated field. You could use a simple iif statement, depending on whether there're any other possible values/nulls to consider, or possibly a switch statement.
e.g.
NewFieldName: IIf([OldFieldName]="Have Pricing","Lost","Won")
or:
NewFieldName: IIf(Nz([OldFieldName],"Have Pricing")="Have Pricing","Lost","Won")
will use Lost for nulls.
or:
NewFieldName: IIf(Nz([OldFieldName],"This was a null!")="Have Pricing","Lost","Won")
will use Won for nulls.
or using switch instead:
NewFieldName: Switch(Nz([OldFieldName],"null!")="null!","Lost",[OldFieldName]="Have Pricing","Lost",[OldFieldName]<>"Have Pricing","Won")
However, if you used the word criteria in the sense that you're wanting to create a parameter query then I'm lost on your purpose as supplying a criteria value that is different than the values in the field will never result in the query having any results.
You want to display the word "Won" if the value is either Active or Closed, and the word "Lost" if the value is Have Pricing?
If that's correct, then you would use a calculated field. You could use a simple iif statement, depending on whether there're any other possible values/nulls to consider, or possibly a switch statement.
e.g.
NewFieldName: IIf([OldFieldName]="Have Pricing","Lost","Won")
or:
NewFieldName: IIf(Nz([OldFieldName],"Have Pricing")="Have Pricing","Lost","Won")
will use Lost for nulls.
or:
NewFieldName: IIf(Nz([OldFieldName],"This was a null!")="Have Pricing","Lost","Won")
will use Won for nulls.
or using switch instead:
NewFieldName: Switch(Nz([OldFieldName],"null!")="null!","Lost",[OldFieldName]="Have Pricing","Lost",[OldFieldName]<>"Have Pricing","Won")
However, if you used the word criteria in the sense that you're wanting to create a parameter query then I'm lost on your purpose as supplying a criteria value that is different than the values in the field will never result in the query having any results.
it is really unnecessary for them to say "won or Lost" I would just like to combine active and closed into a single criteria. I am creating a bar chart to show the jobs awarded (active, closed) vs jobs not awarded(have Pricing). is that a little clearer???
CraigDolphin
GrumpyOldMan in Training
- Local time
- Today, 06:06
- Joined
- Dec 21, 2005
- Messages
- 1,578
Yep. So use the calculated field solution in a totals select query and you should be able to do that. 
Similar threads
