hi
I have created a Crosstab query
the headers are the days of the month ie January 1-31
April 1-30
no problem with the query
however how do i show this data on a form/ report that
will automatically show the days for the month
so if the user selected January i would like the fileds to show 1 to 31
if the user selected April i would like the fileds to show 1 to 30
can this be done without having to create a report for each month


thanks


kevin

kevcar40 wrote:
" and then ask how to get a report to show those headers? I think we need
more details.

when i create the report say for April
the days are 1, 2, 3 , 4 .......30
and the report shows fields / labels 1 - 30
if i create for March
the days are 1, 2, 3 , 4 .......31
but the report only shows fields / labels 1 - 30
i would like the fields/labels to change to reflect the number of days in the month

kevcar40 wrote:
Ah, now I understand. That is a problem that has existed since A97. I was
hoping they would have fixed that by A2010.
Err ... what version of Access are you using?

Anyways, here are some search results that can get you started:

at the moment 2003
but hopefully soon 2010
reading your reply looks like i going to have to create several reports with default days (yuk)

Bob Barrows wrote:
Hmm, how did that happen? I pressed ctrl-c and the message sent itself ...
Anyways:
http://www.google.com/search?q=MS%20...=hp&channel=np

This one seems particularly pertinent:

Bob Barrows wrote:

kevcar40 wrote:

You could use code in the header's format event that checks
the month and makes the inappropriate days invisible:

Select Case monthfield
Case 2
Me.lbl30.Visible = False
Me.txt30.Visible = False
Me.txt30.ControlSource = ""
Me.lbl31.Visible = False
Me.txt31.Visible = False
Me.txt31.ControlSource = ""
If yearfield Mod 4 Then
Me.lbl29.Visible = False
Me.txt29.Visible = False
Me.txt29.ControlSource = ""
End If
Case 4,6,9,11
Me.lbl31.Visible = False
Me.txt31.Visible = False
Me.txt31.ControlSource = ""
End Select

--
Marsh

kevcar40 <kevcar40 (AT) btinternet (DOT) com> wrote in
news:f375b9ec-421f-433c-b7b3-65ded6273f9f (AT) glegroupsg2000goo (DOT) googlegro
ups.com:

First, in the crosstab query, bring up the properties box.
In the Query properties row titled column headings, insert the string
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 ,21,22,23,24,25,26,
27,28,29,30,31

Now the query will produce a column for each of those numbers, even
if there is no data for that day. This is also useful for making a
column if the 12 of April as example has no data.

Design the form or report to show all those columns. You can then
write some code to make the column and the header invisible if the
day is greater than the number of days in the selected month.

A handy way of getting the number of days in a month is
DaysInMonth = day(DateSerial(year(mydate),month(mydate)+1,0))
which correctly handles year-ends



--
Bob Q.
PA is y I've altered my address.

kevcar40 wrote:
http://www.fmsinc.com/tpapers/access...hly/index.html