IIF using Min function

[maw230]

The query returns 2 Month1 values. They are always different, so there is always a Min or Max. The code I am using is:

Month ID: IIf([Month1]=Min([Month1]),1,2)

I want to return a 1 if it's the min and a 2 if it's the max.

The query always returns a 1, presumably because it is only checking that row for the Month1 values when I need it to check the entire column.

In Excel this would be:

=IF(E1=MIN(E:E), 1,2)

How can I tell the query to check all of the records for Month1 and not just that row?

 

[plog]

The query returns 2 Month1 values.

That isn't possible. A query can only return one value per record per named field--you can't have 2 fields named "Month1".

I believe this is what you want:

Month_ID: IIf([Month1]=DMin("[Month1]", "YourTableNameHere"),1,2)

Change YourTableNameHere to the name of the table Month1 is in.

 

[maw230]

That isn't possible. A query can only return one value per record per named field--you can't have 2 fields named "Month1".

i.e. It can only return one of two values - 1 or 2.

Month1 isn't stored in a table. It's a value created within the query by this code:

Month1: Format([Month],"General Number")

 

[maw230]

Nevermind good sir! A little tweaking of your code and it works just fine. Thanks!