Make header display horizontal not vertical?

[TammyT]

Okay, my report has a date header, breaks on each day.

What I want is to have it display the dates across the page, not down - sounds easy enough, but I have not clue! Has to be done in the header - not the details. It's one field (called Date) only, not several fields that I can spread across.

 

[TammyT]

Hey - be proud of me! I did that yesterday - I put them in the date header, & they work! I was even able to format them (I have them in twice) to show the weekday in the top row, & the M/D in the bottom row. I've got them "cosmetically" set as well - nice & even!!!

So, Column titles are in. What's next?

 

[TammyT]

Correction - I put the "columns" (meaning a row of text boxes!) in the PAGE header.

 

[dhookom]

If your text boxes are named in a consistent manner like:
txtCol1, txtCol2,... you can find out how many days are in the month of the date entered into your form. This could be used to hide columns if you really need to.

See if you can figure out how many days might be in the month ending [Forms]![frmQASurveyReports]![EndDateChoice].

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TammyT]

Wait - I haven't found the answer yet; however, in my searching through MS Help (pitiful, I know!), I came across something that might make it easier than writing the report.

There's a way to design & publish info to an Excel worksheet, which would allow me to create a nice graph as well. Would that be "easier" than what we're trying to do here?

I'm going to search through the FAQ on this site to see if I get anything on days in a month.

And yes, I'd need to not only hide, but ensure the data was ignored (there wouldn't be data, due to the criteria we added in the query, right?), for any columns that are not needed due to shorter months.

 

[TammyT]

Well, I found this in the FAQ:

****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the number of calendar days in month
'In: dteDate is the date to be checked
'Out: Returns number of calendar day in month
'Example: DaysInMonth(#1/4/03#) returns 31
'****************************************

Dim dtmTemp As Date

dtmTemp = LastDayOfMonth(dtmDate)
DaysInMonth = CInt(Format(dtmTemp, "dd"))

End Function

Public Function NextWorkDay(dtmDate As Date) As Date

'****************************************

I'm guessing that, if I run that code when the report opens, I would then add something to the last few columns that says if the result of that function is 28 or 30, then hide the column?

 

[dhookom]

Assuming your column heading text box and detail text box have names like:
txtCol0... txtCol30
and
D0...D30
You could use code in the On Open event of the report:

'txtCol27 - txtCol30 should be for dates at the
'  beginning of the month. If not, they are from
'  the previous month and we want them to be invisble
Dim intCol as Integer
For intCol = 27 to 30
   If Day(Me("txtCol" & intCol))>3 Then
      Me("txtCol" & intCol).Visible = False
      Me("D" & intCol).Visible = False
   End If 
Next

What's this about graphing? If you wanted to produce a graph, you should have stated this very early on. A graph might have been easier to create from the start. Access does a fairly decent job with most types of graphs.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TammyT]

I need the details written out as well as a graph. Sorry!!

Am I allowed to email you info outside of this forum? If so - I can email you the Excel file that's been used up until now, so you can see how the report should look.

I'm following the basics of the code okay (I do a bit better w/VBA than SQL just due to experience with them). However, where did intCol come from - I see it defined (Dim), & then used, but I don't see how the VBA arrives at the value for it.

Also - why >3 ? I know that it's correct, just help me understand it!

Then we can move on to totals?

 

[dhookom]

Remember me asking about if you created any consistency when naming your controls on your report? The code would depend on naming the column heading text boxes as well as the detail text boxes. If you want to reference them with code, you need to know what their names will be.

The code creates a little loop with an intCol value that starts at 27 and loops to 30. The code uses this value in the name of your consistently named controls and finds out if they are the end of the previous month or the start of the reporting month. The Day values of these should be<3 if they are the start. If they are the end of the previous month, the values would be 29,30,31 which you would want to make invisible.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TammyT]

Okay - now I understand!! Here's the code as I entered it (remember - I have 2 columns with the same data info, just different display formatting):

Private Sub Report_Open(Cancel As Integer)

'ColWD27 - ColWD30 & ColDate27 - ColDate30 should be for dates at the
' beginning of the month. If not, they are from
' the previous month and we want them to be invisble

Dim intCol As Integer
For intCol = 27 To 30
If Day(Me("ColWD" & intCol)) > 3 Then
Me("ColWD" & intCol).Visible = False
Me("ColDate" & intCol).Visible = False
Me("D" & intCol).Visible = False
End If
Next

End Sub


When I try to run it I get an error that I have entered an expression that has no value (it's run-time error # 2427)

When I click on Debug - the line of code that is highlighted is

If Day(Me("ColWD" & intCol)) > 3 Then

Is my formatting making the problem, or is it that I just have "ColWD" instead of "ColWD27" (through 30)?

FYI - ColWD (1-30) are for displaying the weekdays as Mon, Tue, etc; ColDate (1-30) are for displaying the dates in the format of 4/15, 4/16, etc.

The text boxes are all located in the Page Header right now; nothing in the Details section yet.