First and Last functions 1

[bamafan2002]

I have a report that has several sub reports in it and within each subreport there is at least two subreports. I have data for fiscal years 2001 through 2005. There is missing information in some 2001 fields but for the most part many of the fields do contain data for 2001.

Ex.

Total caseload

2001 - 14250
2002 - 18376
2003 - 21967
2004 - 23128
2005 - 25432


The first query list all information for each year. The second query for for the second report has the following 4 fields for the caseload field.

Fcsld: csld ' record
Lcsld: csld ' last record
Ccsld: [Lcsld] - [Fcsld] ' Subtracts 2005 csld from 2001 csld.
Dcsld: [Ccsld]/[Fcsld] ' Determines percentage of growth between 2001 and 2005.

The problem is that sometimes 2001 does not have data and it needs to move to either 2002 or 2003 depending on whether 2002 has data and if not move to 2003. How can I do this?

 

[rac2]

You might take a slightly different approach and display Not Available where the data is missing and the calculations cannot be done. Use the IIf() function or the IsNull() function. This may be a bit tricky as Not Available is text and the calculations are numbers. You may settle for zeros meaning n/a.

 

[bamafan2002]

I actually solved the problem by developing a query that would not pull those records that had a null value. Then from that query I built another query with the first and last functions. It then did not matter if it was for year 2001, 2002 or 2003 and it compared the values to 2005.

Thanks so much for your help!

Burks

 

[bamafan2002]

Actually this is a two fold question. (1) I am developing a database that titles of documents are linked to the pdf version of the document. For example, there is a form called frmFADocuments and based on a parameter query it can pull up documents for any year as the parameter query ask the user what year they wish to view for documents. That is pretty straight forward... but what I would like to do as have the default come up on 2005 documents... no problem there, but then I would like to have links for 2004, 2003, 2002, 2001. My problem is that I could develop queries for those specific years, but is it possible to create one query and based on the user input whether they choose 2002 or 2003 that the userinput would be passed to the parameter and then the query would pull the correct year? (2) On a parameter query, access brings up a dialog box to enter the parameter such as 2002. Is there a way to copy that information entered in the parameter dialog box to a field in the form without having to copy and paste?

Thanks for any assistance anyone can offer.

Bama (Roll Tide 8-0)

 

[PHV]

One way is to have your own form for parameters value.
Then in the query you replace the [Enter parameter] like strings by something like [Forms]![your parameter form]![textbox]
Note: the parameter form must be opened and populated when the query is launched.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886