Date: 2015-09-21 20:33Subject: RE: Re: [MS_AccessPros] How can I change the delimiter "," into "-" like: Paris-Tours-Dijon?It looks like you put it in the 7th parameter which is the display for "no data"
Bob Peterson
.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, September 21, 2015 8:29 AM
To: MS_Access_Professionals
Subject: Re: Re: [MS_AccessPros] How can I change the delimiter "," into "-" like: Paris-Tours-Dijon?
John-
I did it like this: LoopAndCombine("tblCity","CountryID","City",[CountryID],,,"-"). It doesn't work. what's wrong with this? Thanks.
Kevin
Regards,
Kevin Zhao
Date: 2015-09-21 20:15
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
(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
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 (7) |
Tidak ada komentar:
Posting Komentar