Reference Qry Column

[briank]

I am trying to reference a column in the same query (DateCalc2) for purposes
of grouping. When I run the query, I get a message: that MS Access can't
find the name of the column in my expression. I have read that referencing
of this sort is not the best way to go but cannot think of an alternative.
Any help would be appreciated.

SELECT Format([Date],"mmm yy") AS DateCalc, Format([Date],"yyyymm") AS
DateCalc2, Avg([On Task]+[Transition Movement]) AS TotalClients,
Avg(DSum("[On Task]+[Transition Movement]","tblFlexRatings"," [Hospital No] =
Forms![frmMain]![frmClient].Form![HospitalNo] and [DateCalc2]=
Format([Date],'yyyymm') ")) AS TotalClient
FROM tblFlexRatings
WHERE (((tblFlexRatings.Date) Between
[Forms]![frmFlexRatingsPopup]![BeginningDate] And
[Forms]![frmFlexRatingsPopup]![EndingDate]))
GROUP BY Format([Date],"mmm yy"), Format([Date],"yyyymm")
ORDER BY Format([Date],"yyyymm");

 

[Rick Brandt]

I am trying to reference a column in the same query (DateCalc2) for
purposes of grouping. When I run the query, I get a message: that MS
Access can't find the name of the column in my expression. I have
read that referencing of this sort is not the best way to go but
cannot think of an alternative. Any help would be appreciated.

You cannot reference an alias name. You have to repeat the expression that
has been assigned the alias name...

and Format([Date],"yyyymm") = Format([Date],'yyyymm')

....which as you can see when written this way is a meaningless thing to do
because it will be true 100% of the time. Seems to me you don't need that
in there at all.

Or is one of those "[Date]" entries supposed to be the current date "Date()"
instead of the field you have named "Date" (bad idea since that is a
reserved word).

 

[Ofer Cohen]

The field "DateCalc2" is created in the query, and then you are using the
DSum to filter on that field.
Does this field "DateCalc2" exist in "tblFlexRatings" table, or just in the
query?

If it's in the query, the DSum won't recognize this field
If it exist in the table, then you can't create a new field in the query
with the same name (Format([Date],"yyyymm") As DateCalc2)

 

[briank]

Very good points both of you. My goal of referencing the [DateCalc2] field
(note: not in supporting table but a query calc) in my dsum calculation is to
draw only those numbers that is allocated towards [Hospital No] and
[DateCalc2] . Without this second parameter I get the same number in each
record in this dsum column that I am calling [TotalClient]. So, if this
methodology will not work, can an alternative approach be suggested? I have
successfully tried the make table approach but when multiple people use this
at the same time I get negative results. Any ideas?