transpose textbox's horizontally?

[broj1]

Help need it.
I have a crosstab query.

TRANSFORM Sum(Kontejner_sub.index_zahtevnosti_ctn) AS SumOfindex_zahtevnosti_ctn
SELECT Kontejner_sub.index_zahtevnosti_ctn, Kontejner_sub.datum_zacetka, Projekt.prodajalec
FROM (Kupec INNER JOIN Projekt ON Kupec.IdKupec = Projekt.IdKupec) INNER JOIN Kontejner_sub ON Projekt.IdProjekt = Kontejner_sub.IdProjekt
WHERE (((Projekt.prodajalec)="trimo"))
GROUP BY Kontejner_sub.index_zahtevnosti_ctn, Kontejner_sub.datum_zacetka, Projekt.prodajalec
ORDER BY Kontejner_sub.datum_zacetka
PIVOT Format([datum_zacetka],"ww") In (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,
32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53);

When i put these field on the report (from fields list..select all, grab and drop), they are sorted vertical - but i need it to be horizontal.
Is there a way to traspose auto. or i am force to do it manually...this is like a lot of sorting to do!!??
thx

 

[dhookom]

I am not aware of any automatic method. I suppose you could write some code that loops through the controls and looks at the control source property and possibly sets the Left property based on the value of the Control Source property.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[broj1]

well i did it manually ..... ufff it is so time wasting .
if somebody finds the answer to this please let as know

 

[dhookom]

Here is some sample code. You need to first select all the text boxes that you want to span evenly across your report. Then set all their Tag properties to "align".

Function AlignRptTextBoxes()
    Dim rpt As Report
    Dim ctrl As Control
    Dim lngPageWidth As Long
    Dim intCtrlCount As Integer
    Dim dblCtrlWidth As Double
    Set rpt = Reports("rptWeeksCrosstab")
    lngPageWidth = rpt.Width
    For Each ctrl In rpt.Controls
        If ctrl.Tag = "align" Then
            intCtrlCount = intCtrlCount + 1
        End If
    Next
    dblCtrlWidth = lngPageWidth / intCtrlCount
    For Each ctrl In rpt.Controls
        If ctrl.Tag = "align" Then
            ctrl.Width = dblCtrlWidth
            ctrl.Top = 0
            ctrl.Left = (ctrl.ControlSource - 1) * dblCtrlWidth
        End If
    Next
End Function

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[broj1]

thx dhookom
this comes to me a bit late ... but thanks for the effort, i will sure use it next time