How to access the user's input (to a crosstab query) as a field?

[sueb]

I have a crosstab query that gathers all records for a fiscal year specified by the user's input, then counts the number of records that fall within a specific month, grouped by a discriminant in those records. It's my Surgery database, so I ask the user to enter the 4-digit year that begins the fiscal year; then I calculate the beginning/ending dates for the FY and use those to filter Date of Surgery; then I count the number of records (just using the AutoNumber field) for each Service (General, Ob-Gyn, etc.), grouping them by month.

Here's the SQL as it stands:

TRANSFORM Count(DataTable.Number) AS CountOfNumber
SELECT DataTable.Service, Count(DataTable.Number) AS [Total Of Number]
FROM DataTable
WHERE (((DataTable.[Date of Surgery]) Between CDate("7/1/" & [Enter FY Start Year (yyyy)]) And CDate("6/30/" & ([Enter FY Start Year (yyyy)]+1))))
GROUP BY DataTable.Service, [Enter FY Start Year (yyyy)] & ([Enter FY Start Year (yyyy)]+1)
PIVOT Format([date of surgery],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
 

and I've attached a page of the report for visual reference.

For the life of me, I can't seem to get the entered Fiscal Year to show up as a field that I can print in the report's title. I've tried a couple of different things, including "making a field of it" in a separate (Expression) column:

FY: [Enter FY Start Year (yyyy)]

but that simply disappears when I re-open the query. It doesn't even object to it, but simply refuses to recognize it.

[Stewart Ross]

Hi Sue. In your query the user-entered parameter is not available as a field for you to refer to. It is part of the WHERE clause of your SQL, and you will not be able to retrieve it as a value unless you list it also in the fields you select.

In general it is not good practice to use such parameter queries as the basis for a report at all. The user has no guide as to the form of the value required, and in any event you already have values in your tables which you should be able to show to your users in the form of a pick-list from which they select a value.

Accordingly, I suggest that you design a user-form with a combo box providing a drop-down list for the user to select the relevant year before opening the report on the click of a command button. There are many good examples of how to do this kind of thing around, including in the NorthWind example database available as part of your MS Access installation (or separately downloadable if you do not have direct access to it).

You will find lots of useful techniques on designing good user interfaces like this in books such as Building Microsoft Access Applications by John L. Viescas, which I can recommend you read.

-Stewart

[sueb]

Oh, yes, Stewart, of course this is how to do this! (so obvious once it's said...)

In addition, I'm very grateful for the recommendation of a good book. I don't have an adequate one, and haven't been able to tell (just from reading reviews on Amazon) which to buy. Double thanks for that!