Query Problem - Urgent!

[Guest]

I have a query that uses a Date field to select all records between
#1/1/2006# and #3/31/2006#. In the same query, I have a field named "DO_Qtr1"
(Deposits Outstanding for first quarter) that uses the following expression:

DO_Qtr1: Sum(IIf([Assistant_Retail_Manager_Activities].[Month]=(select
max(Assistant_Retail_Manager_Activities.Month ) from
Assistant_Retail_Manager_Activities),[Assistant_Retail_Manager_Activities].[Deposits_Outstanding],0))

The problem is, this expression is looking for the maximum month in the
query result set (April), but the Where criteria restricts the query to
records for January through March. So, on the DO_Qtr1 field, all that is
displayed for January, March, and April are zeroes.

What and how do I change either the DO_Qtr1 field or the Month field so that
the most recent amount for is displayed in DO_Qtr1.

Help!

 

[OfficeDev18 via AccessMonster.com]

Gwen,

What happens when you lose the Max() function? Seems to me it should look
better.

Sam

I have a query that uses a Date field to select all records between
#1/1/2006# and #3/31/2006#. In the same query, I have a field named "DO_Qtr1"
(Deposits Outstanding for first quarter) that uses the following expression:

DO_Qtr1: Sum(IIf([Assistant_Retail_Manager_Activities].[Month]=(select
max(Assistant_Retail_Manager_Activities.Month ) from
Assistant_Retail_Manager_Activities),[Assistant_Retail_Manager_Activities].[Deposits_Outstanding],0))

The problem is, this expression is looking for the maximum month in the
query result set (April), but the Where criteria restricts the query to
records for January through March. So, on the DO_Qtr1 field, all that is
displayed for January, March, and April are zeroes.

What and how do I change either the DO_Qtr1 field or the Month field so that
the most recent amount for is displayed in DO_Qtr1.

Help!

 

[Guest]

I'm not really interested in having the query "look better" - I just want it
to work! I need the Max() because the query is supposed to select the most
recent month's data.

I have it working now (revised code posted below), but even though the query
now works properly, the results still won't show up on the report based on
the same query. I can't help but wonder if Max() works slightly worse in
Access 2003 (which I'm using) than Access 2002 (which was was using until six
months ago). And I really wish the guy who set this up hadn't used Max()!

DO_Qtr1: Sum(IIf([Assistant_Retail_Manager_Activities].[Month]=(select
max(Assistant_Retail_Manager_Activities.Month ) from
Assistant_Retail_Manager_Activities WHERE
Assistant_Retail_Manager_Activities.Month BETWEEN #1/1/2006# and
#3/31/2006#),[Assistant_Retail_Manager_Activities].[Deposits_Outstanding],0))

Gwen,

What happens when you lose the Max() function? Seems to me it should look
better.

Sam

I have a query that uses a Date field to select all records between
#1/1/2006# and #3/31/2006#. In the same query, I have a field named "DO_Qtr1"
(Deposits Outstanding for first quarter) that uses the following expression:

DO_Qtr1: Sum(IIf([Assistant_Retail_Manager_Activities].[Month]=(select
max(Assistant_Retail_Manager_Activities.Month ) from
Assistant_Retail_Manager_Activities),[Assistant_Retail_Manager_Activities].[Deposits_Outstanding],0))

The problem is, this expression is looking for the maximum month in the
query result set (April), but the Where criteria restricts the query to
records for January through March. So, on the DO_Qtr1 field, all that is
displayed for January, March, and April are zeroes.

What and how do I change either the DO_Qtr1 field or the Month field so that
the most recent amount for is displayed in DO_Qtr1.

Help!