Transform Query and Form Data

[shelby55]

Hi

I am using Access 2003.

I have an unbound report that has 10 graphs on it. Each graph has an SQL statement as its row source. I have a report dialog that is queried for the start date for the report and also the Nursing Unit.

This method works great for "normal" graphs but I'm now trying to add a graph that is using a transform query as its row source:

TRANSFORM Avg(FLO_Data.TotalDays) AS ALOS
SELECT Format([DisDate],"mmm") AS Dmonth
FROM FLO_Data
WHERE (((FLO_Data.Disp) Like "2*"))
GROUP BY Format([DisDate],"mmm"), IIf(Month([DisDate])=1,10,IIf(Month([DisDate])=2,11,IIf(Month([DisDate])=3,
12,IIf(Month([DisDate])=4,1,IIf(Month([DisDate])=5,2,IIf(Month([DisDate])=6
,3,IIf(Month([DisDate])=7,4,IIf(Month([DisDate])=8,5,IIf(Month([DisDate])=9
,6,IIf(Month([DisDate])=10,7,IIf(Month([DisDate])=11,8,IIf(Month([DisDate])
=12,9))))))))))))
ORDER BY IIf(Month([DisDate])=1,10,IIf(Month([DisDate])=2,11,IIf(Month([DisDate])=3,
12,IIf(Month([DisDate])=4,1,IIf(Month([DisDate])=5,2,IIf(Month([DisDate])=6
,3,IIf(Month([DisDate])=7,4,IIf(Month([DisDate])=8,5,IIf(Month([DisDate])=9
,6,IIf(Month([DisDate])=10,7,IIf(Month([DisDate])=11,8,IIf(Month([DisDate])
=12,9))))))))))))
PIVOT FLO_Data.Fyear;

I don't know if it's correct but the order by section is so the months will sort in fiscal month order (Apr to Mar).

Anyway, when I try to add the criteria of "Unit = forms!frmGraphDialog!lstInst3" I get an error message indicating "The Microsoft Jet database engine does not recognize forms!frmGraphDialog!lstInst3 as a valid field name or expression".

Checking TekTips I see that querying in this fashion isn't allowed by transform queries...so how can I have user input for this report if not by form? Thanks.

 

[SkipVought]

hi,

I'd use yyyy-mm and your sort will be correct if your date criteria range is apr to mar.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

In a crosstab query you have to define the parameters, eg:

PARAMETERS forms!frmGraphDialog!lstInst3 TEXT;
TRANSFORM ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi

Thanks for the replies.

Skip, I want the crosstab to be month down the left and fiscal across the top so if I went with your suggestion I'd have months without data and odd data points.

PHV...thanks for the suggestion which I've used.

What would I do to denote the range of months. Right now it is in the format of mmm which is still recognized as text, correct? So how would I pick a start and end range for the months?

Thanks.

 

[shelby55]

Hi

Sorry but I just noticed something with PHV's solution - I get a message box asking for the criteria but not where it is accepting it from a dialog box i.e. even if I enter into the form I have it still prompts me again, I'm assuming, from the query prompt as suggested. Can it not just take the criteria from the separate form?

Also, there are 10 graphs on the report so each is going to have a separate SQL query as the row source so if I'm using in this fashion, it's querying me 10 times for the parameter value.

Is there any way to do this in VBA to assign the values for the Unit and Month range? Thanks.