根据文本框值更改查询列名称。 [英] Change query column name based on text box value.
问题描述
我尝试使用交叉表查询创建月度趋势图。
我的目标是从名为 StartDate 以过滤器的形式：
12个月由数字1到12表示为列名。
真实问题是将列名称（1到12）转换回月份和年份。
最简单的转换方法是：
•第1列是[Forms]！ [过滤]！[StartDate]
•第2列是DateAdd（m，1，[Forms]！[Filter]！[StartDate]）
•列3是DateAdd（m，2，[Forms]！[Filter]！[StartDate]）等
•第12列是DateAdd（m，11，[[表格] ]！[过滤器]！[StartDate]）
但是，MS访问将完全拒绝接受上面的列名转换。
有谁知道如何根据文本框值转换列名？我几乎浪费了大部分时间来破解我的头脑而没有即将发生的事情。
我想，将一些变量声明为文本，为其指定文本框值并将其声明为查询列名称可能是前进的方向，但只是在十字路口。 ??
感谢一些方向和帮助。
I have tried to create a monthly trend graph using cross tab query.
My aim is to extract the last 12 months starting from date specified in a text box called StartDate in a form called Filter:
The 12 months are represented by numbers 1 to 12 as column names.
The real problem is converting the column names ( 1 to 12)back to month and year.
The easiest way to convert is:
•Column 1 is [Forms]! [Filter]![StartDate]
•Column 2 is DateAdd("m",1,[Forms]! [Filter]![StartDate] )
•Column 3 is DateAdd("m",2,[Forms]! [Filter]![StartDate] ) etc. to
•Column 12 is DateAdd("m",11,[ [Forms]! [Filter]![StartDate])
However, MS access will totally refuses to accept the above column name conversion.
Does anyone know how to convert the column names based on text box values? I almost wasted most of my time cracking my head and nothing forthcoming.
I guess, declaring some variables as text, assign text box values to it and declare it as query column name could be a way forward but just at the cross roads. ??
Appreciate some direction and assistance.
推荐答案
你可以从这里开始：
http://allenbrowne.com/ser-67.html
还有一个关于在该页面内设置动态月度交叉表查询的链接。
You might start here:
http://allenbrowne.com/ser-67.html
There''s also a link about setting up a dynamic monthly cross-tab query within that page.
感谢ZMBD的链接..
其实我已经实现了。
我已经完成了以下动态交叉表查询基于这个网站：
.................... .............................. ................. > 例如：
•表格：frmA
•文本框：txtEndDate
•表：tblSales
•字段：SaleDate
您希望在交叉表报表的列中显示12个月的销售额。
设置查询参数通过菜单：查询|参数并输入：
表格！frmA！txtEndDate日期/时间
将此表达式用于列标题：
ColHead：Mth &安培; DateDiff（" m"，[SaleDate]，Forms！frmA！txtEndDate）
这将生成Mth0，Mth1，Mth2，Mth3，...其中Mth0是与结尾相同月份的销售额表格上的日期。 Mth1是上个月等。
步骤1：将查询列标题属性设置为：
列标题：Mth0， ; Mth1，Mth2，Mth3，......，Mth11
基于这些相对构建报告。个月。
如果报表中需要列标签，请使用控制源为的文本框：
步骤＃2 = DateAdd（ " m"，0，Forms！frmA！txtEndDate）
= DateAdd（" m"， - 1，Forms！frmA！txtEndDate）
= DateAdd（" ; m" - 2，Forms！frmA！txtEndDate）
= DateAdd（" m"， - 3，Forms！frmA！txtEndDate） ...
........................................... ....... ..................
现在问题出在第2步。
因为我将填充图表，我想将步骤＃2中格式化的结果作为查询的列名称。
如果是在报告中，然后步骤＃2会很好，因为格式化将在报告上完成，但我希望格式化结果是查询的列标题。
例如，如果在表单上指定的日期是02/12/2011然后当在查询中应用格式时我希望查询标题从Mth1更改为12月12日，Mth2到1月12日，Mth3到2月12日等等。这样我的图表可以从中选择统计数据查询。
非常感谢..
Thanks ZMBD for the link..
Actually I have acheived that already.
I have done the below for dynamic cross tab query based on this site:
.................................................. .................
For instance:
•Form: frmA
•Text Box: txtEndDate
•Table: tblSales
•Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the query parameters by menuing: Query|Parameters and enter:
Forms!frmA!txtEndDate Date/Time
Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)
This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the same month as the ending date on your form. Mth1 is the previous month etc.
Step 1: Set your queries Column Headings property to:
Column Headings: "Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months.
If you need column labels in your report, use text boxes with control sources of:
Step#2=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)...
.................................................. ..................
Now the probelm is in Step#2.
Because I will be populating graphs I want to ue the result of the formating in step#2 as the column name for the query.
If it was in the reports then Step#2 would be just fine because the formating will be done on the report but I want the formating result to be the column heading for the query.
For example if date specified on form is 02/12/2011 then when formating is applied in query I want query heading to change from Mth1 to Dec 11,Mth2 to Jan 12, Mth3 to Feb 12 etc.. so that my graph can pick stats from the query.
Thanks alot..
不可否认，交叉表查询是我数据库知识的一个弱点。通常快速而肮脏的巫师会为我所做的事情工作。然而，我很有希望链接到AB的网站或其中的链接会有所帮助。
我会关注这个帖子，因为我觉得我在这里要学到很多东西。
-z
Admittedly, crosstab queries are a weak point in my database knowledge. Usually the quick and dirty Wizards work for the stuff I do. I was however hopeful that either the link to AB’s site or the one within it would help.
I’ll be following this thread as I think I have much to learn here.
-z
这篇关于根据文本框值更改查询列名称。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
