ACCESS FEED: Re: Re: [MS_AccessPros] How can I change the delimiter "," into "-" like: Paris-Tours-Dijon?

Senin, 21 September 2015

Re: Re: [MS_AccessPros] How can I change the delimiter "," into "-" like: Paris-Tours-Dijon?

John-

I see. Let me try. Thanks a lot.

Kevin

Regards,

Kevin Zhao

From: John Viescas JohnV@msn.com [MS_Access_Professionals]
Date: 2015-09-21 20:15
To: MS_Access_Professionals
Subject: Re: [MS_AccessPros] How can I change the delimiter "," into "-" like: Paris-Tours-Dijon?

Kevin-

The function is already set up to do that. Just supply the sixth parameter. If you don't provide it, it defaults to a comma.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Sep 21, 2015, at 1:40 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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
>
>
>

Regards,
Kevin Zhao