John, Crystal,
The code you wrote few years ago appears the delimiter "," in between strings. My question: is it possible to change "," to "-". The output expected should be Paris-Tours-Dijon. Please help!
Best Regards,
Kevin
================Function Start (Create a Module======================
Function LoopAndCombine(pTablename As String, pIDFieldname As String, pTextFieldname As String, pValueID As Long, Optional pWhere As String = "", Optional pDeli As String = ", ", Optional pNoValue As String = "", Optional pOrderBy As String = "") As Variant
'PARAMETERS
'pTablename --> tablename or queryname to get list from (ie:"qExpenseItemsTotal)
'pIDFieldname --> fieldname to link on (ie: "ExpenseID")
'pTextFieldname --> fieldname to combine (ie: "ItemAndTotal")
'pValueID --> actual value of ID for this iteration ( ie: [ExpenseID])
'pWhere, Optional --> more criteria
'pDeli, Optional --> delimiter other than comma (ie: ";", Chr(13) & Chr(10))
'pNoValue, Optional --> value to use if no data (ie: "No Data")
'crystal 5-6-07, modified 090911, John corrected 14-09-09
'strive4peace2008 at yahoo dot com
On Error GoTo Proc_Err
LoopAndCombine = Null
Dim r As dao.Recordset, mAllValues As String, S As String
mAllValues = ""
S = "SELECT [" & pTextFieldname & "] " & " FROM [" & pTablename & "]" & " WHERE [" & pIDFieldname & "] = " & pValueID & IIf(Len(pWhere) > 0, "And " & pWhere, "") & IIf(Len(pOrderBy) > 0, " ORDER BY " & pOrderBy, "") & ";"
Set r = CurrentDb.OpenRecordset(S, dbOpenSnapshot)
Do While Not r.EOF
If Not IsNull(r(pTextFieldname)) Then
mAllValues = mAllValues & Trim(r(pTextFieldname)) & pDeli
End If
r.MoveNext
Loop
If Len(mAllValues) > 0 Then
mAllValues = Left(mAllValues, Len(mAllValues) - Len(pDeli))
LoopAndCombine = Trim(mAllValues)
ElseIf Len(pNoValue) > 0 Then
LoopAndCombine = pNoValue
End If
r.Close
Proc_Exit:
Set r = Nothing
Exit Function
Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " LoopAndCombine"
Resume Proc_Exit
End Function
=================End====================
====在form和report中的应用====
=LoopAndCombine("ttblSpotKeyword","SpotID","SpotKeyword",[SpotID])
注意：SpotID在form中是真实存在的才可以，如果用一个unbound form，就不行了。
===============================================
John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
> --- On Sat, 9/12/09, newqingqing?wrote:
> Dear All,
> Is that possible to display the record horizontally in report? Thanks!
> Regards,
> Kevin
>
>
>
Function LoopAndCombine(pTablename As String, pIDFieldname As String, pTextFieldname As String, pValueID As Long, Optional pWhere As String = "", Optional pDeli As String = ", ", Optional pNoValue As String = "", Optional pOrderBy As String = "") As Variant
'PARAMETERS
'pTablename --> tablename or queryname to get list from (ie:"qExpenseItemsTotal)
'pIDFieldname --> fieldname to link on (ie: "ExpenseID")
'pTextFieldname --> fieldname to combine (ie: "ItemAndTotal")
'pValueID --> actual value of ID for this iteration ( ie: [ExpenseID])
'pWhere, Optional --> more criteria
'pDeli, Optional --> delimiter other than comma (ie: ";", Chr(13) & Chr(10))
'pNoValue, Optional --> value to use if no data (ie: "No Data")
'crystal 5-6-07, modified 090911, John corrected 14-09-09
'strive4peace2008 at yahoo dot com
On Error GoTo Proc_Err
LoopAndCombine = Null
Dim r As dao.Recordset, mAllValues As String, S As String
mAllValues = ""
S = "SELECT [" & pTextFieldname & "] " & " FROM [" & pTablename & "]" & " WHERE [" & pIDFieldname & "] = " & pValueID & IIf(Len(pWhere) > 0, "And " & pWhere, "") & IIf(Len(pOrderBy) > 0, " ORDER BY " & pOrderBy, "") & ";"
Set r = CurrentDb.OpenRecordset(S, dbOpenSnapshot)
Do While Not r.EOF
If Not IsNull(r(pTextFieldname)) Then
mAllValues = mAllValues & Trim(r(pTextFieldname)) & pDeli
End If
r.MoveNext
Loop
If Len(mAllValues) > 0 Then
mAllValues = Left(mAllValues, Len(mAllValues) - Len(pDeli))
LoopAndCombine = Trim(mAllValues)
ElseIf Len(pNoValue) > 0 Then
LoopAndCombine = pNoValue
End If
r.Close
Proc_Exit:
Set r = Nothing
Exit Function
Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " LoopAndCombine"
Resume Proc_Exit
End Function
=================End====================
====在form和report中的应用====
=LoopAndCombine("ttblSpotKeyword","SpotID","SpotKeyword",[SpotID])
注意：SpotID在form中是真实存在的才可以，如果用一个unbound form，就不行了。
===============================================
John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
> --- On Sat, 9/12/09, newqingqing?wrote:
> Dear All,
> Is that possible to display the record horizontally in report? Thanks!
> Regards,
> Kevin
>
>
>
Regards,
Kevin Zhao
__._,_.___
Posted by: "zhaoliqingoffice@163.com" <zhaoliqingoffice@163.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar