Thread: Date Format
View Single Post
  #8  
Old May 19th, 2010, 05:40 PM posted to microsoft.public.access.reports
AccessKay via AccessMonster.com
external usenet poster
 
Posts: 22
Default Date Format

Marshall...thanks for trying to help me. I've been testing my report and I've
found that it's not always pulling the right totals. So, I need to fix this
first.

AccessKay wrote:
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.

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 - 14 lines]
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