使用WHERE条件连接文本字段 [英] Concatenating text fields with a WHERE condition
问题描述
我有一个问题，使用Dev Ashish的优秀模块将一个字段的
结果从几条记录连接到一条记录。
I我正在使用该代码将某些奖项连结到年底的
证书上。我的代码工作正常，除了我想在某些日期之间将条目限制为奖励的事实
，即使我可以使用限制显示实际记录的查询，当fConcatChild函数运行时，它会获取所有条目，无论日期限制是否为
。我尝试将桌面部分作为一个qry而不是
a tbl，但没有快乐。我认为Dev'模块中的代码需要得到
有日期限制。我需要的限制类型是
WHERE日期>开始日期<结束日期。
有没有人知道如何在模块中做到这一点。
该模块中的代码超出了我的专业知识。
我的代码如下：
**** ***************************************
函数fConcatChild（strChildTable As String， _
strIDName As String，_
strFldConcat As String，_
strIDType As String，_
varIDvalue As变体）_
As String
''从一个1：M关系的Many表中返回一个字段
''在一个半冒号分隔格式。
''
''用法示例：
''？fConcatChild（" Order Details"，" OrderID" ，数量，_
长，10255）
''凡订单明细=多边表
''OrderID =一边桌子的主要钥匙
'数量=连接的字段名称
''长=单边表主键的数据类型
''10255 =返回连接的数量数量
''
Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria作为String，strSQL As String
On Error GoTo Err_fConcatChild
varConcat = Null
设置db = CurrentDb
strSQL ="选择[" &安培; strFldConcat& 来自["] &安培; strChildTable& "]"
strSQL = strSQL& "其中
Select Case strIDType
Case" String"：
strSQL = strSQL& " [" &安培; strIDName& "] =''" &安培; varIDvalue& "''"
Case" Long"，" Integer"，" Double"：''AutoNumber is Type Long
strSQL = strSQL& " [" &安培; strIDName& "] =" &安培; varIDvalue
Case Else
GoTo Err_fConcatChild
结束选择
设置rs = db.OpenRecordset（ strSQL，dbOpenSnapshot）
''我们确定''sub''记录存在
使用rs
如果.RecordCount <> 0然后
''开始连接记录
Do while not rs.EOF
varConcat = varConcat& rs（strFldConcat）& vbCrLf
.MoveNext
循环
结束如果
结束
''那就是它......你现在应该有一个串联的字符串
''只需修剪尾随;
fConcatChild = Left（varConcat，Len（ varConcat） - 2）
Exit_fConcatChild：
设置rs =无：设置db = Nothing
退出功能
Err_fConcatChild：
简历Exit_fConcatChild
结束功能
**************** ***********
除了试图让这个模块按照我的意愿去做，我还是使用了
一个maketable查询把我想要使用的条目放到一个临时的
表中，然后在那个数据上运行这个函数，但是很高兴知道
如何修改有限制的模块。
TIA
Dixie
PS抱歉这篇文章的长度。
I have a problem using Dev Ashish''s excellent module to concatenate the
results of a field from several records into one record.
I am using the code to concatenate certain awards onto a certificate at the
end of the year. I have the code working fine, except for the fact that
when I want to restrict the entries to awards between certain dates, even
though I can use the restriction in the query that shows the actual records,
when the fConcatChild function runs, it picks up all the entries, regardless
of the date restriction. I tried to run the table part as a qry rather than
a tbl, but no joy. I think the code inside Dev''s module will need to get
have the date restriction in it. I need the type of restriction that is
WHERE Date >start date <End date.
Does anyone know how to do that within the module.
The code in that module is beyond my expertise.
The code I have is as follows:
***************************************
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String
''Returns a field from the Many table of a 1:M relationship
''in a semi-colon separated format.
''
''Usage Examples:
'' ?fConcatChild("Order Details", "OrderID", "Quantity", _
"Long", 10255)
''Where Order Details = Many side table
'' OrderID = Primary Key of One side table
'' Quantity = Field name to concatenate
'' Long = DataType of Primary Key of One Side Table
'' 10255 = Value on which return concatenated Quantity
''
Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild
varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
strSQL = strSQL & " Where "
Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = ''" & varIDvalue & "''"
Case "Long", "Integer", "Double": ''AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
''Are we sure that ''sub'' records exist
With rs
If .RecordCount <> 0 Then
''start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & vbCrLf
.MoveNext
Loop
End If
End With
''That''s it... you should have a concatenated string now
''Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 2)
Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function
***************************
Apart from trying to get this module to do as I wish it, I had though of
using a maketable query to put the entries I wish to use into a temporary
table, then running this function on that data, but it would be nice to know
how to modify the module with a restriction.
TIA
Dixie
PS sorry about the length of this post.
推荐答案
你必须通过StartD ate和EndDate进入函数，所以它b / b $ b应该是这样的：
函数fConcatChild（strChildTable为String，_
strIDName As String，_
strFldConcat As String，_
strIDType As String，_
varIDvalue As Variant _
dtmStart As Date，_
dtmEnd As Date，_
As String
'' - 功能体（为简洁起见，大部分省略！）
结束功能
然后在此之后：
选择案例strIDType
案例" String"：
strSQL = strSQL& " [" &安培; strIDName& "] =''" &安培; varIDvalue&
"''"
Case" Long"，" Integer"，" Double"：''AutoNumber is Type Long
strSQL = strSQL& " [" &安培; strIDName& "] =" &安培; varIDvalue
Case Else
GoTo Err_fConcatChild
结束选择
您需要输入日期过滤WHERE子句的一部分
strSQL = strSQL& "和[SomeDate] BETWEEN＃" &安培; dtmStart& ＃AND＃ &
dtmEnd&"＃"
见？什么都没有，对吧？
You have to pass the StartDate and EndDate into the function, so it
should look like this:
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant _
dtmStart As Date, _
dtmEnd As Date, _
As String
''--Function body (mostly omitted for brevity!)
End Function
then after this:
Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = ''" & varIDvalue &
"''"
Case "Long", "Integer", "Double": ''AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select
You need to drop in the date filtering part of the WHERE clause
strSQL = strSQL & " AND [SomeDate] BETWEEN #" & dtmStart & "# AND #" &
dtmEnd &"#"
see? nothing to it, right?
Dixie写道：
我有一个问题，使用Dev Ashish的优秀模块来连接从几个记录到一个记录的字段的结果。
我正在使用代码将某些奖项连接到年底的证书上。我的代码工作正常，除了当我想在某些日期之间将条目限制为奖励时，甚至
虽然我可以在显示实际记录的查询中使用限制，<当fConcatChild函数运行时，它会获取所有条目，无论日期限制是什么。我试着将桌子部分作为一个qry而不是一个tbl，但没有快乐。我认为Dev'模块中的代码需要获得日期限制。我需要的限制类型是
WHERE日期>开始日期<结束日期。
有没有人知道如何在模块中做到这一点。
代码在那里模块超出了我的专长。
我的代码如下：
************************ ***************
函数fConcatChild（strChildTable为String，_
strIDName为String，_
strFldConcat为String，_
strIDType作为字符串，_
varIDvalue As Variant）_
As String
''返回一个字段，来自Many表中的1：M关系
''以分号分隔格式。
''
''用法示例：
''？fConcatChild（" Order Details"，" OrderID"，" Quantity"，_
" Long" ;，10255）
''订单明细=许多边桌
''OrderID =一边的主键ta ble
''Quantity =要连接的字段名称
''Long =单边表主键的数据类型
''10255 =返回连接的值数量
''
Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria as String，strSQL As String
On Error GoTo Err_fConcatChild
> varConcat = Null
设置db = CurrentDb
strSQL ="选择[" &安培; strFldConcat& 来自["] &安培; strChildTable& "]"
strSQL = strSQL& "其中
Select Case strIDType
Case" String"：
strSQL = strSQL& " [" &安培; strIDName& "] =''" &安培; varIDvalue& "''"
CaseLong，Integer，Double：'AutoNumber是Type Type
strSQL = strSQL& " [" &安培; strIDName& "] =" &安培; varIDvalue
Case Else
GoTo Err_fConcatChild
结束选择
设置rs = db.OpenRecordset（strSQL，dbOpenSnapshot）
''我们是确保''sub''记录存在
使用rs
如果.RecordCount<> 0然后
''开始连接记录
Do While not rs.EOF
varConcat = varConcat& rs（strFldConcat）& vbCrLf
.MoveNext
循环
结束如果
''那就是它......你现在应该有一个串联的字符串< br'>''修剪尾随;
fConcatChild =左（varConcat，Len（varConcat） - 2）
退出_fConcatChild：
设置rs = Nothing：设置db = Nothing
退出函数
Err_fConcatChild：
恢复Exit_fConcatChild
结束功能
********************** *****
除了试图让这个模块按照我的意愿去做之外，我还是使用了一个maketable查询将我希望使用的条目放入临时
表，然后在该数据上运行此函数，但很高兴知道如何修改模块有限制。
TIA
Dixie
I have a problem using Dev Ashish''s excellent module to concatenate the
results of a field from several records into one record.
I am using the code to concatenate certain awards onto a certificate at the
end of the year. I have the code working fine, except for the fact that
when I want to restrict the entries to awards between certain dates, even
though I can use the restriction in the query that shows the actual records,
when the fConcatChild function runs, it picks up all the entries, regardless
of the date restriction. I tried to run the table part as a qry rather than
a tbl, but no joy. I think the code inside Dev''s module will need to get
have the date restriction in it. I need the type of restriction that is
WHERE Date >start date <End date.
Does anyone know how to do that within the module.
The code in that module is beyond my expertise.
The code I have is as follows:
***************************************
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String
''Returns a field from the Many table of a 1:M relationship
''in a semi-colon separated format.
''
''Usage Examples:
'' ?fConcatChild("Order Details", "OrderID", "Quantity", _
"Long", 10255)
''Where Order Details = Many side table
'' OrderID = Primary Key of One side table
'' Quantity = Field name to concatenate
'' Long = DataType of Primary Key of One Side Table
'' 10255 = Value on which return concatenated Quantity
''
Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild
varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
strSQL = strSQL & " Where "
Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = ''" & varIDvalue & "''"
Case "Long", "Integer", "Double": ''AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
''Are we sure that ''sub'' records exist
With rs
If .RecordCount <> 0 Then
''start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & vbCrLf
.MoveNext
Loop
End If
End With
''That''s it... you should have a concatenated string now
''Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 2)
Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function
***************************
Apart from trying to get this module to do as I wish it, I had though of
using a maketable query to put the entries I wish to use into a temporary
table, then running this function on that data, but it would be nice to know
how to modify the module with a restriction.
TIA
Dixie
PS sorry about the length of this post.
我不知道你在哪里限制日期范围。代码
没有任何限制它的地方。
Dev'的代码放入要从中返回的列的名称表格
ID =传递的关键值。
如果您知道日期范围是什么，可以将另一个参数传递给
函数。例如
''调用函数前的示例代码
Dim strTable As String
Dim strColumnToConcat As String
Dim strFieldNameOfKey As String
Dim strKeyValue As Variant
Dim strKeyType As String
Dim strDateRestrict As String
strTable ="订单明细>
strColumn ="数量"
strFieldNameOfKey =" OrderID"
strKeyValue = 10255
strKeyType =" Long"
***********
''场景1：你在表格上有一个从/到日期
''现在我们假设你在表格上有一个FromDate和一个ToDate
''让's假设在表中日期字段被称为DateFld
''记住，日期字段被＃
包围如果不是IsNull（Me.FromDate）那么
strDateRestrict =" DateFld> =＃" &安培; Me.FromDate& "＃"
Endif
如果不是IsNull（Me.FromDate）而不是IsNull（Me.ToDate）那么
strDateRestrict = strDateRestrict &安培; "并且
Endif
如果不是IsNull（Me.FromDate）那么
strDateRestrict = strDateRestrict& _
" DateFld< =＃" &安培; Me.ToDate& "＃"
Endif
********
******** ***
''场景2：你有一个From / To date变量
''如果你没有来自/到目前为止，这是怎么回事它会工作
''带变量。再次，表中的字段被假定为
''被称为DateFld
Dim datFrom As Date
Dim datTo As Date
''因为日期字段已经变暗，它们被初始化
''到12/30/1899所以检查一下，不是空的
如果年份（datFrom）<> 1899年然后
strDateRestrict =" DateFld> =＃" &安培; datFrom& "＃"
Endif
如果年份（datFrom）<> 1899年和年（datFromDate）<> 1899年
strDateRestrict = strDateRestrict& "并且
Endif
如果年份（datTo）<> 1899年然后
strDateRestrict =" DateFld< =＃" &安培; datTo& "＃"
Endif
********
strTable ="订单详情"
strColumnToConcat ="数量"
strFieldNameOfKey =" OrderID"
strKeyValue = 10255
strKeyType ="很长的
fConcatChild（strTable，strFieldNameOfKeystrColumnToConcat，_
strKeyType，strKeyValue，strEDEatRestrict）
现在，该函数需要有新的论点，我会称之为
strDateFilter。将它添加到函数中。
函数fConcatChild（strChildTable为String，_
strIDName As String，_
strFldConcat As String，_
strIDType As String，_
varIDvalue As Variant，
strDateFilter As String）As String
Now你需要在打开
记录集之前检查日期过滤器。如果您传递了它，请将其添加到SQL语句中。
更改上面的代码以包含以下内容
如果strDateFilter<> "" Tnen
strSQL = strSQL& "并且 &安培; strDateFilter
结束如果
设置rs = db.OpenRecordset（strSQL，dbOpenSnapshot）
I don''t see where you are restricting by a date range. And the code
doesn''t have any place for restricting it.
Dev''s code puts in the name of the column to return from a table where
the ID = a key value passed.
If you know what the date range is, you could pass another argument to
the function. For example
''sample code prior to calling the function
Dim strTable As String
Dim strColumnToConcat As String
Dim strFieldNameOfKey As String
Dim strKeyValue As Variant
Dim strKeyType As String
Dim strDateRestrict As String
strTable = "Order Details"
strColumn = "Quantity"
strFieldNameOfKey = "OrderID"
strKeyValue = 10255
strKeyType = "Long"
***********
''Scenario 1: You have a from/To date on a form
''now we''ll assume you have a FromDate and a ToDate on the form
''let''s assume in the table the date field is called DateFld
''remember, date fields are surrounded by #
If Not IsNull(Me.FromDate) Then
strDateRestrict = "DateFld >= #" & Me.FromDate & "#"
Endif
If Not IsNull(Me.FromDate) And Not IsNull(Me.ToDate) Then
strDateRestrict = strDateRestrict & " And "
Endif
If Not IsNull(Me.FromDate) Then
strDateRestrict = strDateRestrict & _
"DateFld <= #" & Me.ToDate & "#"
Endif
********
***********
''Scenario 2: You have a From/To date variable
''if you didn''t have a from/to date, this is how it would work
''with a variable. Again, the field in the table is assumed to
''be called DateFld
Dim datFrom As Date
Dim datTo As Date
''since the date fields have been dimmed, they are initialized
''to 12/30/1899 so check for that, not null
If Year(datFrom) <> 1899 Then
strDateRestrict = "DateFld >= #" & datFrom & "#"
Endif
If Year(datFrom) <> 1899 And Year(datFromDate) <> 1899 Then
strDateRestrict = strDateRestrict & " And "
Endif
If Year(datTo) <> 1899 Then
strDateRestrict = "DateFld <= #" & datTo & "#"
Endif
********
strTable = "Order Details"
strColumnToConcat = "Quantity"
strFieldNameOfKey = "OrderID"
strKeyValue = 10255
strKeyType = "Long"
fConcatChild(strTable, strFieldNameOfKeystrColumnToConcat, _
strKeyType, strKeyValue, strEDEatRestrict)
Now, the function needs to have the new argument and I''ll call it
strDateFilter. Add it to the function.
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant,
strDateFilter As String) As String
Now you need to check for the date filter prior to opening the
recordset. If you passed something it it, add it to the SQL statement.
Change your above code to contain the following
If strDateFilter <> "" Tnen
strSQL = strSQL & " And " & strDateFilter
End If
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
谢谢你Piet，对查询中的字符串做了一些工作，这个
现在工作正常。什么都没有？也许对你而言，但至少我可以按照它的工作方式来跟踪它是如何工作的，并能够修改查询字符串以适应，所以我想b / b
考虑我学到的东西。谢谢你的帮助。
dixie
Thank you Piet, with a little bit of work on the string in the query, this
is now working fine. Nothing to it? Maybe for you, but at least I can
follow how it works and was able to modify the query string to suit, so I
consider I learned something. Thanks for helping.
dixie
这篇关于使用WHERE条件连接文本字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
