Diplay info depending on multiple criteria

[Schon]

I am trying to display a field in a query based on multiple criteria. I have
one field defined this way:

Group: IIf([Alternate Group]="Carbox","02 Carbox","01 Patient Supplies")

I then want this field to calculate next

Group Description:If ([SaleType]="R","05 Rental",[Group Description]

It works with this exception. I want to sort the Group Description
Ascending so that the 01 is first, 02 second, and 05 last.
It asks me to 'Enter Parameter Value' for Group when I run the query.
I think if I make it a Make Table Query without sorting I can then create
another query pulling that table in which I can sort but am hoping to do it
all in this query.

Thanks in advance..

 

[Douglas J. Steele]

The issue is that you've got Group Description as the alias plus you're
referring to it in the expression.

Assuming Group Description is a field in your table, change that to

Group Description:If ([SaleType]="R","05 Rental",[TableName].[Group
Description])

 

[Schon]

I apologize but I put the wrong value. It doesnt refer to itself, it refers
to another field in the SAME QUERY. This is what I have..

Group: IIf([Alternate Group]="Carbox","02 Carbox","01 Patient Supplies")

I then want this field to calculate next in the same query

Group Description:If ([SaleType]="R","05 Rental",[Group]

The issue is that you've got Group Description as the alias plus you're
referring to it in the expression.

Assuming Group Description is a field in your table, change that to

Group Description:If ([SaleType]="R","05 Rental",[TableName].[Group
Description])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am trying to display a field in a query based on multiple criteria. I
have
one field defined this way:

Group: IIf([Alternate Group]="Carbox","02 Carbox","01 Patient Supplies")

I then want this field to calculate next

Group Description:If ([SaleType]="R","05 Rental",[Group Description]

It works with this exception. I want to sort the Group Description
Ascending so that the 01 is first, 02 second, and 05 last.
It asks me to 'Enter Parameter Value' for Group when I run the query.
I think if I make it a Make Table Query without sorting I can then create
another query pulling that table in which I can sort but am hoping to do
it
all in this query.

Thanks in advance..

.