Crsosstab Query Doesn't Recognize Expression in Source Query (1 Viewer)

ALewis06

Registered User.
Local time
Today, 16:24
Joined
Jun 21, 2012
Messages
124
I have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC

The ProjRevMRC field is an expression that reads:
ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_SFADetailMRC_ONLY]![Rev Flow Through],0)

When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. What am I doing wrong? Is there a way for the crosstab query to execute the expression and put zeroes for those months?
 

namliam

The Mailman - AWF VIP
Local time
Today, 21:24
Joined
Aug 11, 2003
Messages
11,696
it should work without any problems assuming you have the query as the source for the crosstab...

Only issue may be if ProjRevDate is not actually a date field, but a text field.
 

ALewis06

Registered User.
Local time
Today, 16:24
Joined
Jun 21, 2012
Messages
124
ProjRevDate is a date field formatted as mmm-yy
 

Rabbie

Super Moderator
Local time
Today, 20:24
Joined
Jul 10, 2007
Messages
5,906
What does the SQL view of the Crosstab Query look like?
 

ALewis06

Registered User.
Local time
Today, 16:24
Joined
Jun 21, 2012
Messages
124
TRANSFORM Sum(qry303e_SFADetailProjRevMRC_ONLY.ProjRevMRC) AS SumOfProjRevMRC
SELECT qry303e_SFADetailProjRevMRC_ONLY.ProdRevType, qry303e_SFADetailProjRevMRC_ONLY.[Rev Lag], qry303e_SFADetailProjRevMRC_ONLY.[Rev Flow Through]
FROM qry303e_SFADetailProjRevMRC_ONLY
GROUP BY qry303e_SFADetailProjRevMRC_ONLY.ProdRevType, qry303e_SFADetailProjRevMRC_ONLY.[Rev Lag], qry303e_SFADetailProjRevMRC_ONLY.[Rev Flow Through]
PIVOT Format([ProjRevDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
 

Users who are viewing this thread

Top Bottom