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

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.

"Alisheba" wrote:

> 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
>
>

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

Alisheba wrote:
> 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
>
>

On Wed, 22 Apr 2009 19:51:02 -0700, Alisheba
<(E-Mail Removed)> wrote:

>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.
--

John W. Vinson [MVP]