Date Format

[AccessKay]

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.

 

[Marshall Barton]

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")

 

[AccessKay via AccessMonster.com]

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!!!

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")

 

[Marshall Barton]

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]

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 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")

 

[AccessKay via AccessMonster.com]

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?

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")

 

[Marshall Barton]

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?

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.

 

[AccessKay via AccessMonster.com]

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 - 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.

 

[AccessKay via AccessMonster.com]

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.

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.
[quoted text clipped - 14 lines]

source filtering, you may be able to use the DSum function
to calculate the previous year's values.

 

[Marshall Barton]

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]

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.

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.