Building simple query, but can't get it right

[adamlaing]

I have a table with primary key fields: Year and Company Name. I want to return records that between the maximum year value and maximum -4. In the year field I have set the following criteria:

Between (Max([1 Balance Sheet]![Year])-4) And (Max([1 Balance Sheet]![Year]))

The error returned is: 'Cannot have aggregate function in WHERE clause'. Is there another way to tell the query to find the maximum year value and include all records between maximum value and maximum value-4 (i.e. records for each company for the past five years).

Another problem is I have tried to include a total field which is the sum of various other fields. The total field turns up blank for all records in which there is not a number in one of the fields. For records which have data in all of the specified fields the correct total appears.

e.g. I say Capital and Surplus total: [1 Balance Sheet]![Common stock]+[1 Balance Sheet]![Preferred stock]+[1 Balance Sheet]![Additional paid in capital]+[1 Balance Sheet]![Retained earnings]+[1 Balance Sheet]![Unrealized investment gains (losses)]+[1 Balance Sheet]![Other -please specify]

If any of the fields from Common Stock to Other -please specify are empty there is no total. If all of the fields have numbers in them for a certain record the correct total appears. Any way to fix this?

Thank you to anyone for some help.

 

[adamlaing]

Found solution to second problem

Ok, I found out how to correct the second problem using the Nz function:

Capital and Surplus total: Nz([1 Balance Sheet]![Common stock],0)+Nz([1 Balance Sheet]![Preferred stock],0)+Nz([1 Balance Sheet]![Additional paid in capital],0)+Nz([1 Balance Sheet]![Retained earnings],0)+Nz([1 Balance Sheet]![Unrealized investment gains (losses)],0)+Nz([1 Balance Sheet]![Other -please specify],0)

Just need some help on the first problem now.

 

[adamlaing]

Any suggestions?

 

[Samoan]

You will need to put sub selects in your query.
The WHERE statement should look something like:

WHERE your_date_criteria >= (SELECT max([Year]) - 4 from [1 Balance Sheet])
AND your_date_criteria <= (SELECT max([Year]) from [1 Balance Sheet])

 

[adamlaing]

Thanks a lot, it's working just how I wanted now. A small thing like that had me running around for hours trying to find out how to do it.