CODEWITHSUNDEEP
ms-access
Mr-Auto
Mr-Auto

Reputation: 51

Access Report groupig by expression

I want to group in my report by value [Order] with exception: if value of [tmp] is equal to 1, they should be grouped together with extra header

I tried

=IIf([tmp]=1;1;[Order])

but it just gives an error "This expression is typed incorrectly ...", basically every expression in grouping with IIf function gives that error, works fine in other parts of access

when i just put

=[Order]

it's fine

Is it just me who don't understand something about how expressions or grouping in access works?

Upvotes: 0

Views: 868

Answers (1)

AHeyne
AHeyne

Reputation: 3455

For grouping in a query there are two possibilites:

  1. Use a UNION query:

    SELECT Max(YourField) FROM YourTable WHERE Tmp <> 1 GROUP BY [Order]
UNION
SELECT Max(YourField) FROM YourTable WHERE Tmp = 1 GROUP BY Tmp

  2. Use a sub query to create a 'GroupField' based on your condition:

    SELECT Max(YourField)
FROM (SELECT *, IIf(Tmp = 1, 1, [Order]) As GroupField FROM YourTable) As SubQuery
GROUP BY GroupField

For grouping only in the report:

You can use the sub query as the record source of your report and then group on the 'virtual' field GroupField:

SELECT *, IIf(Tmp = 1, 1, [Order]) As GroupField FROM YourTable

Info:

Max(YourField) and YourTable are just to get my test query running, because you didn't specify the rest of your query.

Upvotes: 1

Related Questions