By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,435 Members | 2,033 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,435 IT Pros & Developers. It's quick & easy.

A way to use crosstab query fielld names as column headings?

P: n/a
I have looked through the newsgroup for an answer to this but haven't
been able to find anything resembling my situation.

What I want to do is relatively simple, I think. I have a crosstab
query that is bound to a report I am creating. I would like the
column headings to be the name of the correspoding field name. For
example, I have field names ALS, BCD, HLP, and the detail section
prints the values for these fields. I would like the report to look
like this:

Date ALS BCD HLP
------------------------------------------------------

5/1 [alsvalue] [bcdvalue] [hlpvalue]
5/2 etc., etc.

Is there an easy way to reference the names of the fields in the query
so that I can set the value of the column heading text boxes to the
field names? If there's not an easy way, is there -any- way?
Thanks.
-SDH-
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Steve,

From my file - see below my SIG line ---

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Dynamic Crosstab Query Report

Dim LngCurrMonth As Long

Dim MonthsToReport As Integer

Dim MonthCounter As Integer

Dim ColumnName As Variant

' Determine Months To Report. Each Column Is A Month

If Forms!FrmRptVoyagerFuelUseParameterVehTypeNum!EndY ear = _

Forms!FrmRptVoyagerFuelUseParameterVehTypeNum!Begi nYear Then

MonthsToReport = Forms!FrmRptVoyagerFuelUseParameterVehTypeNum!EndM onth - _
Forms!FrmRptVoyagerFuelUseParameterVehTypeNum!Begi nMonth + 1

Else

MonthsToReport = 12 - _ Forms!FrmRptVoyagerFuelUseParameterVehTypeNum!Begi nMonth
+ _ Forms!FrmRptVoyagerFuelUseParameterVehTypeNum!EndM onth + 1

End If

' Set The Control Source Property Of Textboxes In Detail Section.

' The Number Of TextBoxes Equals Months To Report.

' Textboxes Are Named Month1, Month2, Month3, Etc.

For MonthCounter = 0 To MonthsToReport - 1

LngCurrMonth = DateAdd("m", MonthCounter, _

Forms!FrmRptVoyagerFuelUseParameterVehTypeNum!Begi nningDate)

Me("Month" & Trim(CStr(MonthCounter) + 1)).ControlSource = Format$(LngCurrMonth,
_

"mmm_yy")

Next

' Create Column Labels, Subtotals And Totals

' Column Labels Are Named LblMonth1, LblMonth2, Etc.

' There Is A Line Named SubTotalLine1, SubTotalLine2, Etc Above The Subtotal In
Each

Line. Only (MonthsToReport) Lines Are Made Visible.

' There Is A Line Named TotalLine1, TotalLine2, Etc Above The Total In Each

Line. Only (MonthsToReport) Lines Are Made Visible.

' First Column--------------------

MonthCounter = 0

Me("LblMonth" & Trim(CStr(0) + 1)).ControlSource = "= Format(DateAdd('m', 0, _

[Forms]![FrmRptVoyagerFuelUseParameterVehTypeNum]![BeginningDate]),'mmm_yy')"

ColumnName = Format(DateAdd("m", 0, _

[Forms]![FrmRptVoyagerFuelUseParameterVehTypeNum]![BeginningDate]), "mmm_yy")

' Set The Control Source Property Of The SubTotal Text Box And Make The

' SubTotal Line Visible

Me("Subtotal" & Trim(CStr(0) + 1)).ControlSource = "=Sum(" & ColumnName & ")"

Me("SubTotalLine" & Trim(CStr(0) + 1)).Visible = True

' Set The Control Source Property Of The Total Text Box And Make The

' Total Line Visible

Me("Total" & Trim(CStr(0) + 1)).ControlSource = "=Sum(" & ColumnName & ")"

Me("TotalLine" & Trim(CStr(0) + 1)).Visible = True

MonthCounter = MonthCounter + 1

' Second Column--------------------

If MonthCounter > MonthsToReport - 1 Then Exit Sub

Me("LblMonth" & Trim(CStr(1) + 1)).ControlSource = "= Format(DateAdd('m', 1, _

[Forms]![FrmRptVoyagerFuelUseParameterVehTypeNum]![BeginningDate]),'mmm_yy')"

ColumnName = Format(DateAdd("m", 1, _

[Forms]![FrmRptVoyagerFuelUseParameterVehTypeNum]![BeginningDate]), "mmm_yy")

' Set The Control Source Property Of The SubTotal Text Box And Make The

' SubTotal Line Visible

Me("Subtotal" & Trim(CStr(1) + 1)).ControlSource = "=Sum(" & ColumnName & ")"

Me("SubTotalLine" & Trim(CStr(1) + 1)).Visible = True

' Set The Control Source Property Of The Total Text Box And Make The

' Total Line Visible

Me("Total" & Trim(CStr(1) + 1)).ControlSource = "=Sum(" & ColumnName & ")"

Me("TotalLine" & Trim(CStr(1) + 1)).Visible = True

MonthCounter = MonthCounter + 1

"Steve" <hb***@uiuc.edu> wrote in message
news:bb**************************@posting.google.c om...
I have looked through the newsgroup for an answer to this but haven't
been able to find anything resembling my situation.

What I want to do is relatively simple, I think. I have a crosstab
query that is bound to a report I am creating. I would like the
column headings to be the name of the correspoding field name. For
example, I have field names ALS, BCD, HLP, and the detail section
prints the values for these fields. I would like the report to look
like this:

Date ALS BCD HLP
------------------------------------------------------

5/1 [alsvalue] [bcdvalue] [hlpvalue]
5/2 etc., etc.

Is there an easy way to reference the names of the fields in the query
so that I can set the value of the column heading text boxes to the
field names? If there's not an easy way, is there -any- way?
Thanks.
-SDH-

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.