CRITERIA FOR AN EXPRESSION

[Alisheba]

I built the following expression in a query:

61+ Last Month: First(IIf([61+DEC1] Is Not Null,[61+DEC1],IIf([61+NOV] Is
Not Null,[61+NOV],IIf([61+OCT] Is Not Null,[61+OCT],IIf([61+SEPT] Is Not
Null,[61+SEPT],IIf([61+AUG] Is Not Null,[61+AUG],IIf([61+JULY] Is Not
Null,[61+JULY],IIf([61+JUNE] Is Not Null,[61+JUNE],IIf([61+MAY] Is Not
Null,[61+MAY],IIf([61+APRIL] Is Not Null,[61+APRIL],IIf([61+MARCH] Is Not
Null,[61+MARCH],IIf([61+FEB] Is Not Null,[61+FEB],IIf([61+JAN] Is Not
Null,[61+JAN])))))))))))))

Under criteria I'm specifying >5000

I keep getting "Expression to complex in query expression" when I run
query. The query works once I remove the criteria.

Any help would be greatly appreciated.

Thank You

 

[KARL DEWEY]

Change your table structure from a spreadsheet with a field for each month to
a record for each month. You can use a union query to rearange the data.

 

[John Spencer MVP]

You might try an alternate expression like the following
LastMonth: First(NZ([61+Dec1],NZ([61+Nov],NZ([61+OCT],NZ([61+Sept],[61+AUG]))))

But a better solution would probably be to redesign your database structure as
noted elsewhere.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I keep getting "Expression to complex in query expression" when I run
query. The query works once I remove the criteria.

OUCH.

If this is based on a Table, the table structure IS WRONG.
If it's based on a query calculating the 61+ field, rewrite the query.
For a shorter simpler expression using the current mess, use the NZ() function
instead of the nested IIF:

61+ Last Month:
First(NZ([61+DEC1],NZ([61+NOV],NZ([61+OCT],NZ([61+SEPT],NZ([61+AUG],NZ([61+JULY],NZ([61+JUNE],NZ([61+MAY],NZ([61+APRIL],NZ([61+MARCH],NZ([61+FEB],NZ([61+JAN])))))))))))))

If you have a date-of-birth field you can use it directly to see if the person
reached age 61 during the previous calendar month.