A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Date Format



 
 
Thread Tools Display Modes
  #1  
Old May 17th, 2010, 05:59 PM posted to microsoft.public.access.reports
AccessKay
external usenet poster
 
Posts: 106
Default 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  
Old May 17th, 2010, 06:53 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old May 17th, 2010, 08:19 PM posted to microsoft.public.access.reports
AccessKay via AccessMonster.com
external usenet poster
 
Posts: 22
Default 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 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")


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

  #4  
Old May 17th, 2010, 11:49 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old May 18th, 2010, 04:02 PM posted to microsoft.public.access.reports
AccessKay via AccessMonster.com
external usenet poster
 
Posts: 22
Default 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  
Old May 18th, 2010, 07:22 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
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

  #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

  #9  
Old May 19th, 2010, 07:02 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.