Fiscal Date - Quarterly Report Issue

[pbundrant]

I posted this earlier in the week on the Access Reports Forum.
-----------------------------------------------

Hello all,
I am having a wonderful time trying to find the solution to my problem. I have looked and looked and every solution to fiscal year problems, don't apply to my particular issue.

Please help.

I have created a simple report to show all the work orders completed between certain dates (user entered). It groups by quarters but instead of it displaying "Q1 - 2002" I need it to display "Q3 - 2002". Our fiscal year starts July 1.

Is there a quick fix that can be entered into the same field of the report that displays "QX - 200X"? Just to change what is displayed, like an IF statement that I have seen on this message board, but didn't work in my case.

Oh, and I am VB illiterate. I have tweaked it but never written it myself.

Thanks so much....

PMB s-)

 

[mac318]

On your report, place an unbound text box to hold the "Q3-2000" or whichever quarter is appropriate. In the GroupBy Footer for the quarterly stats, place this code:

dim x as string

If mid([FieldHoldingData]2,1) = "1" then
x = "Q3-2000"
Endif

If mid([FieldHoldingData]2,1) = "2" then
x = "Q1-2000"
Endif

If mid([FieldHoldingData]2,1) = "3" then
x = "Q2-2000"
Endif

UnboundTextBoxName = x

Replace [FieldNameHoldingData] with the actual field name used in your database table.

Replace the UnboundTextBoxName with the name you give the unbound textbox.

It would look smarter using a Select statement; however, it would be more difficult for you to code.

mac

 

[pbundrant]

Thank you so much for your response mac,

I did try it backwards and forwards and I couldn't get it to work with that code.

I ended up just creating a text box (Text36), Visible property set to: no, containing the following:

=DateAdd(&quot;m&quot;,+6,[Date]) <--- I saw this in another post.

and then the actual (visible) text box with the following:

=Format$([Text36],&quot;\Qq yyyy&quot;,0,0)

It seems kinda silly, but it works.

Thanks,
PMB :-{}

 

[mac318]

PBM, there's nothing silly about code that works. Besides, you came up with the solution. Happy programming.

mac

 

[dawnd3]

I thank you both for your help. FYI, PMB, I just combined your formulas and put in the same text box and it worked great and saved a step.

=Format$(DateAdd(&quot;m&quot;,+6,[dtmDateUsed]),&quot;\Qq yyyy&quot;,0,0)

Thank you!!!!

Dawn

http://www.calliopeconsulting.net

 

[aud9]

Got any suggestions for calculating fiscal periods where weeks are used instead of months. For ex. 1st fiscal period is 1st 4 wks of the year and then another 4wks then 5 wks, then 4wks, 4wks, 5wks again until the end of calendar year. Meed to be able to group data by this fiscal periods.

Aud9