If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Format
I have two things that I’m trying to do in an unbound text box in my report.
I have this =Format([Forms]![frmVariance]![txtMo2],"q""Qtr""yyyy") which returns “1QTR2010” but I want it to look like “Qtr 1 2010”. I tried this “Qtr” “q” "yyyy" but Access changed it back to "q""Qtr""yyyy". I also need the same thing for the previous year’s quarter like “Qtr 1 2009”. Thanks for the help. |
#2
|
|||
|
|||
Date Format
AccessKay wrote:
I have two things that Im trying to do in an unbound text box in my report. I have this =Format([Forms]![frmVariance]![txtMo2],"q""Qtr""yyyy") which returns 1QTR2010 but I want it to look like Qtr 1 2010. I tried this Qtr q "yyyy" but Access changed it back to "q""Qtr""yyyy". I also need the same thing for the previous years quarter like Qtr 1 2009. You do not need to use Format function to do that. Just set the text box to =[Forms]![frmVariance]![txtMo2] and then set the text box's Format property to a custom format like: "Qtr" q yyyy If you must use the Format function, I think it should be: =Format([Forms]![frmVariance]![txtMo2],"""Qtr"" q yyyy") -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Date Format
Thanks for your reply. I wasn't sure how to do the custom format but the
second option you gave me worked great. But how can I show the previous year's quarter. I have two unbound text boxes on my form where I enter two date periods. If the user enters 3/2010 into the second text box, then the report will show Q1 Total for 2010 in one column but then I have another column on the report (from expression in query) that is the previous year's quarter. I want the report's unbound box to show this. I hope I explained this okay??? Thanks!!! Marshall Barton wrote: I have two things that Im trying to do in an unbound text box in my report. I have this =Format([Forms]![frmVariance]![txtMo2],"q""Qtr""yyyy") which returns 1QTR2010 but I want it to look like Qtr 1 2010. I tried this Qtr q "yyyy" but Access changed it back to "q""Qtr""yyyy". I also need the same thing for the previous years quarter like Qtr 1 2009. You do not need to use Format function to do that. Just set the text box to =[Forms]![frmVariance]![txtMo2] and then set the text box's Format property to a custom format like: "Qtr" q yyyy If you must use the Format function, I think it should be: =Format([Forms]![frmVariance]![txtMo2],"""Qtr"" q yyyy") -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201005/1 |
#4
|
|||
|
|||
Date Format
I don't understand. If you have calculated the previous
year's quarter total in the record source query, just bind the other text box to the calculated field just like you did for this year's quarter total?? -- Marsh MVP [MS Access] AccessKay via AccessMonster.com wrote: Thanks for your reply. I wasn't sure how to do the custom format but the second option you gave me worked great. But how can I show the previous year's quarter. I have two unbound text boxes on my form where I enter two date periods. If the user enters 3/2010 into the second text box, then the report will show Q1 Total for 2010 in one column but then I have another column on the report (from expression in query) that is the previous year's quarter. I want the report's unbound box to show this. Marshall Barton wrote: I have two things that I?m trying to do in an unbound text box in my report. I have this =Format([Forms]![frmVariance]![txtMo2],"q""Qtr""yyyy") which returns ?1QTR2010? but I want it to look like ?Qtr 1 2010?. I tried this ?Qtr? ?q? "yyyy" but Access changed it back to "q""Qtr""yyyy". I also need the same thing for the previous year?s quarter like ?Qtr 1 2009?. You do not need to use Format function to do that. Just set the text box to =[Forms]![frmVariance]![txtMo2] and then set the text box's Format property to a custom format like: "Qtr" q yyyy If you must use the Format function, I think it should be: =Format([Forms]![frmVariance]![txtMo2],"""Qtr"" q yyyy") |
#5
|
|||
|
|||
Date Format
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 to create a report for a quarterly comparison so for example, if the user enters 12/2009 in the first text box and 3/2010 in the second text box, I can use those text boxes for the record source to show Qtr 4 2009, and Qtr 1 2010 under the columns of my report. I still have one column on my report that 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? Marshall Barton wrote: I don't understand. If you have calculated the previous year's quarter total in the record source query, just bind the other text box to the calculated field just like you did for this year's quarter total?? Thanks for your reply. I wasn't sure how to do the custom format but the second option you gave me worked great. But how can I show the previous [quoted text clipped - 19 lines] If you must use the Format function, I think it should be: =Format([Forms]![frmVariance]![txtMo2],"""Qtr"" q yyyy") -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201005/1 |
#6
|
|||
|
|||
Date Format
AccessKay via AccessMonster.com 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 to create a report for a quarterly comparison so for example, if the user enters 12/2009 in the first text box and 3/2010 in the second text box, I can use those text boxes for the record source to show Qtr 4 2009, and Qtr 1 2010 under the columns of my report. I still have one column on my report that needs a reference source for the Previous Years Qtr (Qtr 1 2009). Im 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. -- Marsh MVP [MS Access] |
#7
|
|||
|
|||
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 Years Qtr (Qtr 1 2009). Im 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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
Date Format
The query is a bit difficult to read so I can't be sure, but
it looks like you have the previous quarter and previous year calculations in there. If so, it should just be a matter of debugging the expressions to get the correct result in the report. -- Marsh MVP [MS Access] AccessKay via AccessMonster.com wrote: 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. |
Thread Tools | |
Display Modes | |
|
|