Crosstab Queries - finding header value

D

Deano

Is there anyway to get the value of a header within a crosstable. What I am
trying to do is a planner with a list of items down the left hand side and
dates along the top. When I select a value somewhere within the data area
(example in excel, cell d6) I would like to know what the header value,
which in this case is the date. I'm OK in getting the item selected but no
the date. I have tried to put the date on the left hand side with the item
details but this leaves a lot of items down the left hand side which is not
what I am after. If you can help or if there is some otherway of doing this
I would be greatful.

Thanks in advance
 
D

Duane Hookom

I don't know why and where you would need this. If it is on a form control,
the column heading/name is the Control Source property.
 
D

Deano

I need to get the Header Value so I can show what work is carried out on the
date. I'm trying to create a planner of work due to be carried out over a
period of time using a cross tab so the headers are going to vary (Dates as
Headers) See example below

10/11/2004 11/11/2004 12/11/2004 13/11/2004

Printer 1 5 3 6
2
Printer 2 2 1 4
4
Printer 3 6 3 2
4


When I select on a value (eg Printer 1 on the 12/11/2004), I need to get the
machine id (Printer 1, Which I can do) in the column and then the Header
(12/11/2004 Date which can also vary from time to time). This is so I can
list the work jobs being carried out that machine for that date (In a
seperate listbox show the 6 individual jobs details). If there is another
way to do this please could you advise. I hope I've explain myself properly


Thanks in advance
 
D

Duane Hookom

Again, I need to ask where are you clicking "When I select on a value"? How
are you selecting and where? Is this on a form control or in a datasheet
view?
 
D

Deano

Sorry for not explaining myself properly.

On a form there is a subform/report control which links to a query. It is
here that I am clicking on the value.
The value what I am refering to can be seen below.

10/11/2004 11/11/2004 12/11/2004
Printer 1 5 3 6
Printer 2 2 1 4
Printer 3 6 3 2

The value which I'm clicking is '1'. The relates to Printer 2 on the
11/11/2004 and it is '11/11/2004' that I want to get at. I am OK on getting
'Printer 2' as a value, but I need '11/11/2004'aswell. If I have both
values (Printer 2 and 11/11/2004) I can then link to a listbox/query within
a control on the same form to display the underlying details

If the above is not possible using a Query within a form, maybe a listbox
maybe better but I am stuck with the same situation on getting the header
info.

Hope this helps

Thanks again
 
C

Craig Hornish

Hi,
Not sure if this is exactly what you need but here goes.

In my situation I use a "Crosstab Query" (You had said 'table', wich
might have caused some confusion) in a report that could have different
column headings based on other critea, so I have to use unbound Controls and
then assign the column headings that come up from that query to the
controls.
I use a recordest of the that query and then take the "Name" of the
column I need.

Partial code
Set qdf = db.querydefs("qxtCrosstab")
For Each prm in qdf.Parameters ' Takes care of the criteria I have
prm.Value = Eval(prm.Name)
next prm
Set rst = qdf.OpenRecordset

'What you need
txtUnboundTextBox = rst(intColumn - 1).Name 'Name give you the
heading.

I believe rst are Base 0 for column counts so a intColumn = 2 would
return 10/11/04
Just another situation if you are listing multiple columns then you will
want to have the unbound controls numbered.
i.e. txtUnboundTextBox1,txtUnboundTextBox2, and loop through them
(Me("txtUnboundTextBox" + Format(intLoop)) = ...

Hope that is what you needed,

Craig Hornish
[email protected] - so I can delete it when it becomes a spam magnet

"Think outside the box, because anything is possible."
"How long it will take or whether it requires divine intervention is another
issue"
 
D

Duane Hookom

Going back to my first reply:
"If it is on a form control, the column heading/name is the Control Source
property."
 
Top