format the date text box

[huertalm]

i have created one multiple-column report that will display an employees stats by either week, month, quarter, or year depending on what the user selects. everything works fine but the column heading (which is the date) always displays as the last day. example if the latest stats entered on an employee were on the 15th of march when i pull up the report with the "month" option it will display jan31, feb28, and march15 as the column headings. this is of course good if i choose the "weekly" option but i want it to display "jan", "feb" and "march" for the monthly option, "qtr1", "qtr2, "qtr3, "qtr4" for the quarter option and just the year for the year option. is this possible?

 

[WayneRyan]

huertalm,

One option is to have different reports ... (No)

The other thing you can do is in the OnOpen Event of the
report:

If Forms![YourForm]![YourOption] = "Quarterly" Then
   Me.HeadingBox1 = "Qtr 1"
   Me.HeadingBox2 = "Qtr 2"
   Me.HeadingBox3 = "Qtr 3"
   Me.HeadingBox4 = "Qtr 4"
   Exit Sub
End If

You can use a similar approach for monthly etc.

One thing though, four quarters, 12 months, now THAT will
complicate things.

Wayne

 

[huertalm]

i dont want to have a report for each one because i dont think they would be used too often and i dont want to slow down my program. i already have 6 other reports that do other things so you can just imagine how many that will be if i make one for each period. the column headings arent just a label. its a text box that gets the date from the main table. when i put in each employees stats i put in the week ending date, their name and the stats. so the week-ending date is the date that is being pulled from the table to the report. is there a way to have the format swithced depending on the option the user selects. i know if i create a separate report that will base the stats on the month, the date format is =format$([date],"mmmm",0,0). but when i try to put this in my code where it formats the reports numbers based on the users selection it gives me a message saying "youve entered an expression that has no value".

 

[huertalm]

well ive made some progress:

when i put in this code under the group footer:
Me.EndDate.Format = Format$([EndDate], "mmmm yyyy", 0, 0)

i get this as the heading:

jan0uary4 2003
jan0uary11 2003
etc