Thread: Date Format
View Single Post
  #7  
Old May 18th, 2010, 07:41 PM posted to microsoft.public.access.reports
AccessKay via AccessMonster.com
external usenet poster
 
Posts: 22
Default Date Format

Okay...here is my query:

SELECT tblTrans_Mstr.Category, tblTrans_Mstr.DGroup, tblTrans_Mstr.Product,
tblTrans_Mstr.PA_Descriptions, Sum(IIf([TransDate] Between (Forms!
[frmVariance]!txtMo1) And DateAdd("m",1,(Forms![frmVariance]!txtMo1))-1,
tblTrans_Mstr.Lbr_Total_Cost,0)) AS Month_1, Sum(IIf([TransDate] Between
(Forms![frmVariance]!txtMo2) And DateAdd("m",1,(Forms![frmVariance]!txtMo2))-
1,tblTrans_Mstr.Lbr_Total_Cost,0)) AS Month_2, [Month_2]-[Month_1] AS MoVar,
Sum(IIf(Year([transDate])=Year(CDate([FORMS]![frmVariance]![txtMo2])) And
DatePart("q",[transDate])=DatePart("q",CDate([FORMS]![frmVariance]![TxtMo2])),
[Lbr_Total_Cost],0)) AS QtrTotal, Sum(IIf(Year([transDate])=Year(CDate(FORMS!
[frmVariance]!txtMo2))-1 And DatePart("q",[transDate])=DatePart("q",-1,CDate
(FORMS![frmVariance]!TxtMo2)),[Lbr_Total_Cost],0)) AS PrevQtr, [QtrTotal]-
[PrevQtr] AS VarPrevQtr, Sum(IIf(Year([transDate])=Year(CDate(FORMS!
[frmVariance]!txtMo2))-1 And DatePart("q",[transDate])=DatePart("q",CDate
(FORMS![frmVariance]!TxtMo2)),[Lbr_Total_Cost],0)) AS PrevYrQtr, [QtrTotal]-
[PrevYrQtr] AS VarPrevYrQtr
FROM tblTrans_Mstr
WHERE (((tblTrans_Mstr.Lbr_Total_Cost) Is Not Null And (tblTrans_Mstr.
Lbr_Total_Cost)0))
GROUP BY tblTrans_Mstr.Category, tblTrans_Mstr.DGroup, tblTrans_Mstr.Product,
tblTrans_Mstr.PA_Descriptions
HAVING ((((Sum(IIf([TransDate] Between ([Forms]![frmVariance]![txtMo1]) And
DateAdd("m",1,([Forms]![frmVariance]![txtMo1]))-1,nz([tblTrans_Mstr].
[Lbr_Total_Cost],0),0)))+(Sum(IIf([TransDate] Between ([Forms]![frmVariance]!
[txtMo2]) And DateAdd("m",1,([Forms]![frmVariance]![txtMo2]))-1,nz(
[tblTrans_Mstr].[Lbr_Total_Cost],0),0))))0));

I really appreciate you looking at this. If I can provide anything else,
please let me know.


Marshall Barton wrote:
Let me try to explain better. I have two text boxes on my form where a user
can pick any two periods. I initially set this up for months. But I needed

[quoted text clipped - 4 lines]
needs a reference source for the Previous Year’s Qtr (Qtr 1 2009). I’m not
sure how to do this. Am I making better sense?


It might make more sense if I understood how you are
calculating both this year's and the previous year's month
and quarter values and see the report's record source query.

If you are asking how to calculate the previous year's
values, then I can try to figure it out from how you do it
for this years values. I'm beginning to suspect that this
year's value are calculated by using the Sum function in one
or more group and/or the report footer section text box
expressions. If so, then, depending on the report's record
source filtering, you may be able to use the DSum function
to calculate the previous year's values.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/201005/1