Assigning Crosstab Query Headers to Control Source(s)?

[Lythandra]

Hi,

I have a crosstab query that is working great showing the data that is needed.

I have 3 row headers which are static. Then my Column Headers are dates, of
which there can be many.

I am only going to show 2 weeks (thus 14 boxes) of data at a time and give
the user the ability to go forward/backward 2 weeks at a time.

My question is how do I assign my changing Column Headers (from the crosstab
query) to the Control Sources on the form?

I've searched on here of course but did not find any previous answers.

Thanks

 

[Graham Mandeno]

Hi Lythandra

Presumably the start date is being specified or selected from another form
before the query is opened, and is then used in a WHERE clause in the query.

Instead of using the date field as your column header, use an expression
like this:

Hdr: "D" & ([DateField] - [StartDate])

So, your first column will be "D0", then "D1" and so on up to "D13". You
can then bind these fields to your form controls.

You will also want to change the header labels for the columns in your form
(or report). To do this, give the labels names with the same prefix plus
the day number from 0 to 13 (let's say they are lblD0, lblD1, etc). Now add
this code to your Form_Load or Report_Open event:

Dim i as Integer
For i = 0 to 13
Me( "lblD" & i ).Caption = Format ([StartDate] + i, "d mmm" )
Next i

 

[Lythandra]

Ahhh, thats it. Thankee.

I had done that some years ago but couldn't remember for anything.

Hi Lythandra

Presumably the start date is being specified or selected from another form
before the query is opened, and is then used in a WHERE clause in the query.

Instead of using the date field as your column header, use an expression
like this:

Hdr: "D" & ([DateField] - [StartDate])

So, your first column will be "D0", then "D1" and so on up to "D13". You
can then bind these fields to your form controls.

You will also want to change the header labels for the columns in your form
(or report). To do this, give the labels names with the same prefix plus
the day number from 0 to 13 (let's say they are lblD0, lblD1, etc). Now add
this code to your Form_Load or Report_Open event:

Dim i as Integer
For i = 0 to 13
Me( "lblD" & i ).Caption = Format ([StartDate] + i, "d mmm" )
Next i

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I have a crosstab query that is working great showing the data that is
needed.

I have 3 row headers which are static. Then my Column Headers are dates,
of
which there can be many.

I am only going to show 2 weeks (thus 14 boxes) of data at a time and give
the user the ability to go forward/backward 2 weeks at a time.

My question is how do I assign my changing Column Headers (from the
crosstab
query) to the Control Sources on the form?

I've searched on here of course but did not find any previous answers.

Thanks