如何使用Function传递查询参数？ [英] How to use Function to pass query parameter?
问题描述
我需要一种方法来创建一个具有编程定义名称的表。我有一个
Make Table查询，它将使用我在
查询中输入的名称创建表，但我不知道如何（或者如果我可以）使用函数将表格
名称传递给查询。
这是Mke Tble查询的基本形式：
SELECT TxDate，Amount，Tx_ID，TxAcctName，INTO [此处的表名] FROM
tblTxJournal;
这似乎没有work：
SELECT TxDate，Amount，Tx_ID，TxAcctName，INTO（QryPrmName）FROM
tblTxJournal;
公共函数QryPrmName（）As String
Dim strTxAcct As String
Dim strQtr As String
Dim strYr As String
strTxAcct = Forms！frm2！cbx2TxAcct.Column（1）
strQtr = Forms！frm2！cbx2Quarter.Column（1）
strYr = Forms！frm2！cbx2Year
QryPrmName =" Transactions_For_" &安培; strTxAcct& " _" &安培; strQtr& " _" &安培; str $
结束功能
是否可以这样做？
提前致谢。
I need a way to create a table with a programmatically defined name. I have a
Make Table query that will create the table with the name that I put in the
query, but I don''t know how to (or if I can) use a function to pass the table
name into the query.
Here is the basic form of the Mke Tble query:
SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO [table name here] FROM
tblTxJournal;
This does not seem to work:
SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO (QryPrmName) FROM
tblTxJournal;
Public Function QryPrmName() As String
Dim strTxAcct As String
Dim strQtr As String
Dim strYr As String
strTxAcct = Forms!frm2!cbx2TxAcct.Column(1)
strQtr = Forms!frm2!cbx2Quarter.Column(1)
strYr = Forms!frm2!cbx2Year
QryPrmName = "Transactions_For_" & strTxAcct & "_" & strQtr & "_" & strYr
End Function
Is it possible to do this?
Thanks in advance.
推荐答案
要在现有表中插入行，必须使用INSERT INTO，而不是SELECT
.... INTO。如果要为插入指定目标字段，请在INSERT INTO< table-name>之后立即在括号中列出
。以相同的顺序
它们将出现在SELECT的输出中。
示例：
INSERT INTO tblFoo（FooName， FooDescription）SELECT BarName，BarDescription
FROM tblBar
如果你想用正确的字段名称创建表格而不是
而不是先创建它，但仍然可以控制字段名称，你只需要
需要别名选择列表中的字段。当然，如果你需要一个自动编号，索引，等等，你必须在之后改变表格设计。
示例：
SELECT BarName作为FooName，BarDescription作为FooDescription
INTO tblFoo来自tblBar
周六，2004年1月31日21:57:24 GMT，" ;杰科" < DJ **** @ hotmail.com>写道：
To insert rows into an existing table, you must use INSERT INTO, not SELECT
.... INTO. If you want to specify the target fields for your insert, you list
them in parentheses right after INSERT INTO <table-name> in the same order
they will appear in the output from the SELECT.
Example:
INSERT INTO tblFoo (FooName, FooDescription) SELECT BarName, BarDescription
FROM tblBar
If you want to create the table with the correct field names directy rather
than creating it first, but still have control over the field names, you just
need to alias the fields in your select list. Of course, you''ll have to alter
the table design afterward if you need an Autonumber, indexes, aetc.
Example:
SELECT BarName As FooName, BarDescription As FooDescription
INTO tblFoo FROM tblBar
On Sat, 31 Jan 2004 21:57:24 GMT, "deko" <dj****@hotmail.com> wrote:
我需要一种方法来创建一个具有编程定义名称的表。我有一个
Make Table查询，它将创建具有我在
查询中放置的名称的表，但我不知道如何（或者如果可以）使用函数来传递表格
名称进入查询。
这是Mke Tble查询的基本形式：
SELECT TxDate，Amount，Tx_ID，TxAcctName，INTO [表名]这里]来自
tblTxJournal;
这似乎不起作用：
SELECT TxDate，Amount，Tx_ID，TxAcctName，INTO（QryPrmName）FROM
tblTxJournal;
公共函数QryPrmName（）As String
Dim strTxAcct As String
Dim strQtr As String
Dim strYr As String
strTxAcct = Forms！frm2 ！cbx2TxAcct.Column（1）
strQtr = Forms！frm2！cbx2Quarter.Column（1）
strYr = Forms！frm2！cbx2Year
QryPrmName =" Transactions_For_" &安培; strTxAcct& " _" &安培; strQtr& " _" &安培; strYr
结束功能
是否可以这样做？
提前致谢。
I need a way to create a table with a programmatically defined name. I have a
Make Table query that will create the table with the name that I put in the
query, but I don''t know how to (or if I can) use a function to pass the table
name into the query.
Here is the basic form of the Mke Tble query:
SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO [table name here] FROM
tblTxJournal;
This does not seem to work:
SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO (QryPrmName) FROM
tblTxJournal;
Public Function QryPrmName() As String
Dim strTxAcct As String
Dim strQtr As String
Dim strYr As String
strTxAcct = Forms!frm2!cbx2TxAcct.Column(1)
strQtr = Forms!frm2!cbx2Quarter.Column(1)
strYr = Forms!frm2!cbx2Year
QryPrmName = "Transactions_For_" & strTxAcct & "_" & strQtr & "_" & strYr
End Function
Is it possible to do this?
Thanks in advance.
您好，感谢您的回复。
好吧，也许我可以在创建后重命名该表：
对于每个tdf在CurrentDb.TableDefs中
如果tdf.Name = strOldName那么
''CurrentDb.Execute" DROP TABLE" &安培; strNewName
tdf.Name = strNewName
结束如果
下一页
CurrentDb.TableDefs.Refresh
问题是如果它已经存在则处理表...我还有
麻烦：
DoCmd.OutputTo acOutputTable，strNewName
我可以让它工作但是会出现丑陋的错误...
另一种选择，正如你的建议，首先用正确的
名称创建表并插入其中。我以为我可以使用DDL：
DoCmd.RunSql（" CREATE TABLE strNewName（TxDate Date，Amount Currency，TxAcctName
Text，Tx_ID AutoIncrement Constraint PrimaryKey PRIMARY KEY）;
或类似的东西...
" Steve Jorgensen"< no **** @ nospam.nospam>在留言中写道
新闻：ls ******************************* *@4ax.com ...
Hi and thanks for the reply.
well, perhaps I could just rename the table after creating it:
For Each tdf In CurrentDb.TableDefs
If tdf.Name = strOldName Then
''CurrentDb.Execute "DROP TABLE " & strNewName
tdf.Name = strNewName
End If
Next
CurrentDb.TableDefs.Refresh
the problem is dealing with table if it already exists... I''m also having
trouble with:
DoCmd.OutputTo acOutputTable, strNewName
I can get it to work but getting ugly errors...
The other option, as you suggested, is to create the table first with the right
name and insert into it. I was thinking I could use DDL for this:
DoCmd.RunSql ("CREATE TABLE strNewName (TxDate Date, Amount Currency, TxAcctName
Text, Tx_ID AutoIncrement Constraint PrimaryKey PRIMARY KEY);
or something like that...
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:ls********************************@4ax.com...
要在现有表中插入行，必须使用INSERT INTO，而不是SELECT
... INTO。如果要指定目标字段你的插入，你在INSERT INTO< table-name>中以相同的顺序在括号中列出它们
它们将出现在SELECT的输出中。
示例：
INSERT INTO tblFoo（FooName，FooDescription）SELECT BarName，BarDescription
FROM tblBar
如果你想用正确的字段名创建表而不是创建表而不是创建表它首先，但仍然是哈您可以控制字段名称，只需要对选择列表中的字段进行别名。当然，如果你需要一个自动编号，索引，等等，你将不得不改变表设计。
示例：
SELECT BarName作为FooName，BarDescription作为FooDescription
INTO tblFoo from tblBar
2004年1月31日星期六21:57:24 GMT，deko < DJ **** @ hotmail.com>写道：
To insert rows into an existing table, you must use INSERT INTO, not SELECT
... INTO. If you want to specify the target fields for your insert, you list
them in parentheses right after INSERT INTO <table-name> in the same order
they will appear in the output from the SELECT.
Example:
INSERT INTO tblFoo (FooName, FooDescription) SELECT BarName, BarDescription
FROM tblBar
If you want to create the table with the correct field names directy rather
than creating it first, but still have control over the field names, you just
need to alias the fields in your select list. Of course, you''ll have to alter
the table design afterward if you need an Autonumber, indexes, aetc.
Example:
SELECT BarName As FooName, BarDescription As FooDescription
INTO tblFoo FROM tblBar
On Sat, 31 Jan 2004 21:57:24 GMT, "deko" <dj****@hotmail.com> wrote:
我需要一种方法来创建一个具有编程定义名称的表。我有
aMake Table查询，它将创建具有我在
查询中放置的名称的表，但我不知道如何（或者如果可以）使用函数来传递表
进入查询的名称。
这是Mke Tble查询的基本形式：
SELECT TxDate，Amount，Tx_ID，TxAcctName，INTO [table name here ]来自
tblTxJournal;
这似乎不起作用：
SELECT TxDate，Amount，Tx_ID，TxAcctName，INTO（QryPrmName）FROM
tblTxJournal ;
公共函数QryPrmName（）As String
Dim strTxAcct As String
Dim strQtr As String
Dim strYr As String
strTxAcct = Forms！frm2！ cbx2TxAcct.Column（1）
strQtr = Forms！frm2！cbx2Quarter.Column（1）
strYr = Forms！frm2！cbx2Year
QryPrmName =" Transactions_For_" &安培; strTxAcct& " _" &安培; strQtr& " _" &安培; strYr
结束功能
是否可以这样做？
提前致谢。
I need a way to create a table with a programmatically defined name. I have aMake Table query that will create the table with the name that I put in the
query, but I don''t know how to (or if I can) use a function to pass the table
name into the query.
Here is the basic form of the Mke Tble query:
SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO [table name here] FROM
tblTxJournal;
This does not seem to work:
SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO (QryPrmName) FROM
tblTxJournal;
Public Function QryPrmName() As String
Dim strTxAcct As String
Dim strQtr As String
Dim strYr As String
strTxAcct = Forms!frm2!cbx2TxAcct.Column(1)
strQtr = Forms!frm2!cbx2Quarter.Column(1)
strYr = Forms!frm2!cbx2Year
QryPrmName = "Transactions_For_" & strTxAcct & "_" & strQtr & "_" & strYr
End Function
Is it possible to do this?
Thanks in advance.
2004年1月31日星期六21:57:24 GMT，deko写道：
On Sat, 31 Jan 2004 21:57:24 GMT, deko wrote:
我需要一种方法来创建一个具有编程定义名称的表。我有一个
Make Table查询，它将创建具有我在
查询中放置的名称的表，但我不知道如何（或者如果可以）使用函数来传递表格
名称进入查询。
这是Mke Tble查询的基本形式：
SELECT TxDate，Amount，Tx_ID，TxAcctName，INTO [表名]这里]来自
tblTxJournal;
这似乎不起作用：
SELECT TxDate，Amount，Tx_ID，TxAcctName，INTO（QryPrmName）FROM
tblTxJournal;
公共函数QryPrmName（）As String
Dim strTxAcct As String
Dim strQtr As String
Dim strYr As String
strTxAcct = Forms！frm2 ！cbx2TxAcct.Column（1）
strQtr = Forms！frm2！cbx2Quarter.Column（1）
strYr = Forms！frm2！cbx2Year
QryPrmName =" Transactions_For_" &安培; strTxAcct& " _" &安培; strQtr& " _" &安培; strYr
结束功能
是否可以这样做？
提前致谢。
I need a way to create a table with a programmatically defined name. I have a
Make Table query that will create the table with the name that I put in the
query, but I don''t know how to (or if I can) use a function to pass the table
name into the query.
Here is the basic form of the Mke Tble query:
SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO [table name here] FROM
tblTxJournal;
This does not seem to work:
SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO (QryPrmName) FROM
tblTxJournal;
Public Function QryPrmName() As String
Dim strTxAcct As String
Dim strQtr As String
Dim strYr As String
strTxAcct = Forms!frm2!cbx2TxAcct.Column(1)
strQtr = Forms!frm2!cbx2Quarter.Column(1)
strYr = Forms!frm2!cbx2Year
QryPrmName = "Transactions_For_" & strTxAcct & "_" & strQtr & "_" & strYr
End Function
Is it possible to do this?
Thanks in advance.
如果不需要QueryDef对象，那么执行以下操作将会简化（修改错误句柄以满足您的需要）...
公共函数QryPrmName（）As String
On Error Goto Error_Handler
Dim strTxAcct As String
Dim strQtr As String
Dim strYr As String
Dim strSQL As String
Dim db As Database
strTxAcct = Forms！frm2！cbx2TxAcct.Column（1）
strQtr = Forms！frm2！cbx2Quarter.Column（1）
strYr = Forms！frm2！cbx2Year
strSQL =" SELECT TxDate，Amount，Tx_ID ，TxAcctName，INTO"
strSQL = strSQL& " Transactions_For_" &安培; strTxAcct& " _" &安培; strQtr& " _"
strSQL = strSQL& " strYr"来自tblTxJournal;
db.Execute sqlStr，dbFailOnError
Exit_Here：
退出功能
Error_Handler：
MsgBox Err.Number& " - &安培; Err.Description
简历Exit_Here
结束功能
-
Mike Storr
veraccess.com
If a QueryDef object is not required, then doing the following would
simplify things (modify the error handle to suit your needs)...
Public Function QryPrmName() As String
On Error Goto Error_Handler
Dim strTxAcct As String
Dim strQtr As String
Dim strYr As String
Dim strSQL As String
Dim db As Database
strTxAcct = Forms!frm2!cbx2TxAcct.Column(1)
strQtr = Forms!frm2!cbx2Quarter.Column(1)
strYr = Forms!frm2!cbx2Year
strSQL = "SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO "
strSQL = strSQL & "Transactions_For_" & strTxAcct & "_" & strQtr & "_"
strSQL = strSQL & "strYr" FROM tblTxJournal;
db.Execute sqlStr, dbFailOnError
Exit_Here:
Exit Function
Error_Handler:
MsgBox Err.Number & " - " & Err.Description
resume Exit_Here
End Function
--
Mike Storr
veraccess.com
这篇关于如何使用Function传递查询参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
