A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Need help with concatenation function



 
 
Thread Tools Display Modes
  #1  
Old May 21st, 2010, 06:57 PM posted to microsoft.public.access.queries
EllenM
external usenet poster
 
Posts: 206
Default Need help with concatenation function

Hello,
I've found a wonderful concatenation function at
http://www.mvps.org/access/modules/mdl0008.htm. It, however, separates the
concatenated values with semicolons when I'd like them to be separated with
"br /"s.

For instance, instead of "Value1; Value2; Value3", I'd like "Value1br /
Value2br / Value3br /".

Thanks in advance for your help.

Ellen

  #2  
Old May 21st, 2010, 07:18 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Need help with concatenation function

EllenM wrote:
Hello,
I've found a wonderful concatenation function at
http://www.mvps.org/access/modules/mdl0008.htm. It, however,
separates the concatenated values with semicolons when I'd like them
to be separated with "br /"s.

For instance, instead of "Value1; Value2; Value3", I'd like
"Value1br / Value2br / Value3br /".

Thanks in advance for your help.

Ellen


I don't have the function in front of me, but surely it can't be too hard to
change the '& ";" 'bit to '& "br /"'

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


  #3  
Old May 21st, 2010, 07:26 PM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Need help with concatenation function

On Fri, 21 May 2010 10:57:01 -0700, EllenM
wrote:

Hello,
I've found a wonderful concatenation function at
http://www.mvps.org/access/modules/mdl0008.htm. It, however, separates the
concatenated values with semicolons when I'd like them to be separated with
"br /"s.

For instance, instead of "Value1; Value2; Value3", I'd like "Value1br /
Value2br / Value3br /".

Thanks in advance for your help.

Ellen


Just fix the code to your needs: where it has

With lors
If .RecordCount 0 Then
'start concatenating records
Do While Not .EOF
lovConcat = lovConcat & lors(stFldToConcat) & "; "
.MoveNext
Loop
Else
GoTo Exit_fConcatFld
End If
End With

change it to

With lors
If .RecordCount 0 Then
'start concatenating records
Do While Not .EOF
lovConcat = lovConcat & lors(stFldToConcat) & " br / "
.MoveNext
Loop
Else
GoTo Exit_fConcatFld
End If
End With
--

John W. Vinson [MVP]
  #4  
Old May 21st, 2010, 08:03 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Need help with concatenation function

you would also need to fix the code in one other spot near the bottom of the
routine. If you want "br / " at the end of the string or not.

Either
fConcatFld = lovConcat
OR
fConcatFld = Left(lovConcat, Len(lovConcat) - Len("br / "))
instead of
fConcatFld = Left(lovConcat, Len(lovConcat) - 2)




John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John W. Vinson wrote:
On Fri, 21 May 2010 10:57:01 -0700, EllenM
wrote:

Hello,
I've found a wonderful concatenation function at
http://www.mvps.org/access/modules/mdl0008.htm. It, however, separates the
concatenated values with semicolons when I'd like them to be separated with
"br /"s.

For instance, instead of "Value1; Value2; Value3", I'd like "Value1br /
Value2br / Value3br /".

Thanks in advance for your help.

Ellen


Just fix the code to your needs: where it has

With lors
If .RecordCount 0 Then
'start concatenating records
Do While Not .EOF
lovConcat = lovConcat & lors(stFldToConcat) & "; "
.MoveNext
Loop
Else
GoTo Exit_fConcatFld
End If
End With

change it to

With lors
If .RecordCount 0 Then
'start concatenating records
Do While Not .EOF
lovConcat = lovConcat & lors(stFldToConcat) & " br / "
.MoveNext
Loop
Else
GoTo Exit_fConcatFld
End If
End With

  #5  
Old May 21st, 2010, 08:34 PM posted to microsoft.public.access.queries
EllenM
external usenet poster
 
Posts: 206
Default Need help with concatenation function

Thanks, John

And change:
fConcatFld = Left(lovConcat, Len(lovConcat) - 2)

to
fConcatFld = Left(lovConcat, Len(lovConcat) - 8)

Now it's a masterpiece.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:43 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.