Adding ", " to concatenated text

I have a control that is to display in the On Enter event, a concatenated 
sentence including the users choices in a multi-select list box.  The code is 
mainly from nice people here which I've taken bits from and added to and it 
almost works except for getting a comma and space between the entries.  Here 
is the result I'm getting:
"There is damage to A/C systemCooling systemDecklid/tailgate,."
I want it to be:
"There is damage to the A/C system, Cooling system, Decklid/tailgate."
Here is my code:
Dim varRow As Variant, strText As String
strText = vbNullString
For Each varRow In Me.lbDamagedParts.ItemsSelected
strText = strText & Me.lbDamagedParts.Column(0, varRow)
Me.Text39 = "There is damage to " & strText & ", "
Next varRow
Me.Text39 = Left(Me.Text39, Len(Me.Text39) - 1) & "."

Thank you so much for helping me get through this!!

Pamela  


0
Utf
12/31/2009 3:02:01 PM
access.forms 6864 articles. 2 followers. Follow

4 Replies
840 Views

Similar Articles

[PageSpeed] 56

You need to decrease your string by 2 rather than 1. You have been adding a
comma and a space.
Me.Text39 = Left(Me.Text39, Len(Me.Text39) - 2) & "."


Pamela wrote:
>I have a control that is to display in the On Enter event, a concatenated 
>sentence including the users choices in a multi-select list box.  The code is 
>mainly from nice people here which I've taken bits from and added to and it 
>almost works except for getting a comma and space between the entries.  Here 
>is the result I'm getting:
>"There is damage to A/C systemCooling systemDecklid/tailgate,."
>I want it to be:
>"There is damage to the A/C system, Cooling system, Decklid/tailgate."
>Here is my code:
>Dim varRow As Variant, strText As String
>strText = vbNullString
>For Each varRow In Me.lbDamagedParts.ItemsSelected
>strText = strText & Me.lbDamagedParts.Column(0, varRow)
>Me.Text39 = "There is damage to " & strText & ", "
>Next varRow
>Me.Text39 = Left(Me.Text39, Len(Me.Text39) - 1) & "."
>
>Thank you so much for helping me get through this!!
>
>Pamela

-- 
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

0
ruralguy
12/31/2009 5:06:58 PM
Thanks anyway but I think you misunderstood my post.  I'll try again in 
another way.

"ruralguy via AccessMonster.com" wrote:

> You need to decrease your string by 2 rather than 1. You have been adding a
> comma and a space.
> Me.Text39 = Left(Me.Text39, Len(Me.Text39) - 2) & "."
> 
> 
> Pamela wrote:
> >I have a control that is to display in the On Enter event, a concatenated 
> >sentence including the users choices in a multi-select list box.  The code is 
> >mainly from nice people here which I've taken bits from and added to and it 
> >almost works except for getting a comma and space between the entries.  Here 
> >is the result I'm getting:
> >"There is damage to A/C systemCooling systemDecklid/tailgate,."
> >I want it to be:
> >"There is damage to the A/C system, Cooling system, Decklid/tailgate."
> >Here is my code:
> >Dim varRow As Variant, strText As String
> >strText = vbNullString
> >For Each varRow In Me.lbDamagedParts.ItemsSelected
> >strText = strText & Me.lbDamagedParts.Column(0, varRow)
> >Me.Text39 = "There is damage to " & strText & ", "
> >Next varRow
> >Me.Text39 = Left(Me.Text39, Len(Me.Text39) - 1) & "."
> >
> >Thank you so much for helping me get through this!!
> >
> >Pamela
> 
> -- 
> RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
> Please post back to this forum so all may benefit.
> 
> Message posted via http://www.accessmonster.com
> 
> .
> 
0
Utf
12/31/2009 5:49:01 PM
On Thu, 31 Dec 2009 07:02:01 -0800, Pamela <Pamela@discussions.microsoft.com>
wrote:

>I have a control that is to display in the On Enter event, a concatenated 
>sentence including the users choices in a multi-select list box.  The code is 
>mainly from nice people here which I've taken bits from and added to and it 
>almost works except for getting a comma and space between the entries.  Here 
>is the result I'm getting:
>"There is damage to A/C systemCooling systemDecklid/tailgate,."
>I want it to be:
>"There is damage to the A/C system, Cooling system, Decklid/tailgate."
>Here is my code:
>Dim varRow As Variant, strText As String
>strText = vbNullString
>For Each varRow In Me.lbDamagedParts.ItemsSelected
>strText = strText & Me.lbDamagedParts.Column(0, varRow)
>Me.Text39 = "There is damage to " & strText & ", "
>Next varRow
>Me.Text39 = Left(Me.Text39, Len(Me.Text39) - 1) & "."
>
>Thank you so much for helping me get through this!!
>
>Pamela  
>

You're almost there! Append a comma and a blank for each value you append, and
then trim off the last comma at the end:

Dim varRow As Variant, strText As String
strText = vbNullString
For Each varRow In Me.lbDamagedParts.ItemsSelected
strText = strText & Me.lbDamagedParts.Column(0, varRow) & ", "
Me.Text39 = "There is damage to " & strText & ", "
Next varRow
Me.Text39 = Left(Me.Text39, Len(Me.Text39) - 2) & "."

-- 

             John W. Vinson [MVP]
0
John
12/31/2009 6:07:10 PM
OK, I see what you are after. Try this:

Dim varRow As Integer, strText As String
For Each varRow In Me.lbDamagedParts.ItemsSelected
   strText = strText & ", " & Me.lbDamagedParts.Column(0, varRow)
Next varRow
Me.Text39 = "There is damage to " & strText & ". "



Pamela wrote:
>Thanks anyway but I think you misunderstood my post.  I'll try again in 
>another way.
>
>> You need to decrease your string by 2 rather than 1. You have been adding a
>> comma and a space.
>[quoted text clipped - 20 lines]
>> >
>> >Pamela

-- 
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

0
ruralguy
12/31/2009 6:13:30 PM
Reply:

Similar Artilces:

outlook vcard, ad integration user properties
my boss is trying to get hix fax# in his vcard. he is using his address from the global address list I have modified his profile in active directory users and computers: I go into AD users and computers, double click on his user, go to the telephones tab, and enter his fax #. but when I did this his fax # is still not being pulled up in his outlook vcard. Does anyone know where outlook is trying to get this field from / how i can get this field into his vcard? thx ...

How to format text in the cell, so the end of text will be visible?
In visual basic: When the textbox is smaller than the text, to display the last part of the text I do: txtWaveFile.SelStart = Len(txtWaveFile.Text) How can I do the same for the particular cell? I have tried: moExcelWS.Cells(1, 2).Text.sellstart = Len(moExcelWS.Cells(1, 2).Text) but that of course is wrong. Jack Maybe you can justify the text to the left? But if there's something in the cell to the left, you won't see the leading text. Jack wrote: > > In visual basic: > When the textbox is smaller than the text, to display the last part of the > text I do: >...

Best practice: Visio, is it commonly accepted if text doesn't fit.
I am new to Visio. I created a flowchart with many shapes. I've already scaled and reset orientation. I can't make the shapes large enough to fix the text in them. So, two questions: 1. For best practice, typically should all your shapes be the same size in a flowchart? 2. Is it commonly accepted if your text doesn't fit in a shape? Best practise is uniformity, but as with most rules there are exceptions. A couple of oversized shapes is not as distracting as having most of the shapes in different sizes. Rather having some shapes larger by expanding in width and de...

Rich Edit, text color
I've got a rich edit control named "Ala". I've got two variables: m_Ala (CString) and m_AlaCTRL (control variable). m_ala="ala ma 1 kota"; I want to colorize substring "1". How? Here is some code what I use now: CHARFORMAT format; format.dwMask = CFM_COLOR; format.crTextColor = RGB( 255, 0, 0 ); m_AlaCTRL.SetWordCharFormat(format); It will colorize all text. How can I colorize just a substring? ArtuS > m_ala="ala ma 1 kota"; Add These lines here to select the "1" m_AlaCTRL.SetSel(7,7); > CHARFORMAT format; &...

AD Account added, mailbox not created.
..I am trying to add new user accounts to AD; I type in all the correct information, set up a password and tell AD to create a mailbox. The summary screen says that the mailbox will be created on the server and in the storage group that I indicated. The problem is that, while the AD account itself gets created right away, the Exchange mailbox doesn't get created at all. I know this because we have a Recipient Policy in place to create several different types of addresses, but no addresses appear in ADUC and the mailbox never appears in ESM. Can anyone steer me in the right direc...

I can consolidate data, but what about text items?
Hi, I have got the hand of consolidating data from multiple sources, but when I consolidate non-numeric data - i.e text, it returns 0 by default for these columns. Is there anything I can do to consolidate and retain text items? Thank you! there is only one way to consolidate text. in your to consolidate areas make sure to have text in only the top row or left column. then when you se the date >> consolidate option check the marks. this will not consolidate values, but take only the text from the 1st roe and left column. for aother values in the to consolidate areas you can onl...

Customising Macro Buttons, Adding an image in excel
How do I add an image to a macro button in excel? Hi Click on View|Toolbars|Customise. Then right-click on your macro button and select 'Change button image'. Andy. "Baz" <Baz@discussions.microsoft.com> wrote in message news:82BC5991-777E-46A5-A781-A83004B5A04F@microsoft.com... > How do I add an image to a macro button in excel? ...

text boxes lose formatting, size, and placement
I am using Office 2010 on a Windows 7 machine. I am working with text boxes and I am having the following problems: 1. When I scroll the text box loses its size, place, and sometimes the text in the box. 2. When I toggle betwee show/hide formatting marks, the same thing happens. 3. The Position button options do not place the text box as expected. Is this a beta issue? Is there some default I need to reset? BTW: the document has sections and columns if that is important to know. Thanks...plc This appears to be a display issue. Word makes surprisingly large demands on the displ...

How can I make Excel Files save as the text appers in a cell, can.
Do you mean you want to save a file and have the file named the contents of some cell? I so, you need a macro. The save statement is like this for cell A1. ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & Range ("A1").Value & ".xls" HTH Otto "I khan" <I khan@discussions.microsoft.com> wrote in message news:E61084C0-5841-4510-B0EB-7B3319E60937@microsoft.com... > ...

Cell only shows link in text, not contents of reference cell
Some cells on the worksheet will show the information in the referenced cell. Others only show the statement. For example, if I enter ='log'!K5, that's exactly what appears in that cell, not the contents of the referenced cell. John Since you say that only *some* cells exhibit this problem, I would guess that the "bad" cells were probably formatted as text,*before* you entered the formulas. If that's the case, you must change the formatting *and* re-register the formula in order to obtain a valid XL formula. Try this: Select a "bad" cell, Then <Ctrl...

COUNT multiple columns, numeric & text "URGENT"
Thanks for any help. With some help the other day, the following formula worked for one o the items I was trying to accomplish: count how many in column A tha are 0_F and are II in column K. {=SUM((A1:A5="0_F")*(K1:K5="II"))} A B K 0_F 1 I 0_F 2 II 0_F 1 II 0_R 1 II 0_R 2 I The second item that I would like to count is how many in column B tha are 2 and are II in column K. I tried to use the same formula above, but it does not work. It give me the answer 0 instead of 1. Any suggestions? Th...

MT2OFX
MT2OFX V3 RC1 is availailable on the website http://www.mt2ofx.tk/ . This version includes support for MT940 files and (NL) Postbank ASC, (NL) Rabobank MUT.ASC and (NL) SNS Bank CSV formats.The program builds on a framework for parsing input files and producing OFX or QIF output files, so that adding support for other input formats is a question of creating (copying and modifying) a small script in VBScript. Colin Smale ...

AD, centralized email and DL
I have a Windows 2003 Domain set up in our office. We are also running Office 2003 on all clients. I am trying to find a way to have centralized management of all employees email addresses. I have been testing LDAP to Active Directory through OUTLOOK however I have not found a way to share Distribution Lists. So the goal is to manage all employees email addresses and associated distribution lists centrally so that everyone has access to current information and does not have to maintian their own. Please let me know if you can help with this or have any other ideas.... Thanks Troy. ...

Pivot Table problem, blank fields aren't being added
Hi, I have a Pivot table pulling data from another workbook. It's displaying a count for given values in 2 columns. It's fine for most of the data, but when one of the columns is blank, it shows a count of 0, even though there are items that are blank. If I double click on the field, it shows me a sheet that contains the data that isn't being counted. Is this a bug in the Pivottable? Is there a way around it? Thanks. Ant A pivot table can't count blanks. You could type a space character in those cells, or use a formula: ="" Then, they'll be counted. An...

Sumif using Values, but returning text or values
Hi, I have 2 different reports. One has a list of contracts with a unique id and the other has a much larger list of contracts that also have a unique TEXT id that I need to return to the smaller report. The only common field between both reports is the contract value in dollars. Any help would be great appreciated!! -- I need so much help, but right now, some Excel help will suffice!! Use the VLookup( ) function BUT GOSH, what happens if you have 2 contracts for $1,000.00.???!!! -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your...

how to: if a value is entered in one row, then associated data is cummulatively added to a 'total' on another sheet ?
Excel Version : 12.2.3 OS X 10.6.2 Intel Hi =97 I am very new to Excel, and am learning a lot, but have found it difficult to work out a new problem. I am trying to nut out a formula to do the following (I have searched all over, but realise I don't really know what my search terms should be). For arguments sake, I have two worksheets, A and B. Worksheet A is used as a meta-summary of what will happen elsewhere in the Workbook. Worksheet B is where the raw data is entered (as it will in C, D, E, etc, in the future). I have created a drop down list of categories for use in Wo...

Outlook Rich Text, Attachments, and Winmail.dat
Apparently, an attachment created within Outlook 98 Rich Text Format and sent to Outlook Express comes in as an unusable "winmail.dat" file. But changing Outlook 98 to Plain Text Format enables creating and sending the attachment to Outlook Express in a normal and usable way. My questions are (1) Is the above statement correct and reasoanbly complete as given?, and (2) Would Outlook 98 Rich Text users have a similar problems sending an attachment to other email programs such as Eudora? In other words,is using Rich Text Format on Outlook 98 not a very good idea, unless yo...

windows server 2008 run as 2003 functional mode, how to use a 2003 server as AD
I use windows server 2008 and formatted it as 2003 functional server mode and run active directory domain and then I use another windows server 2003 join into the forest as another active directory domain but they cannot replicate would any one tell me why? how to make them syn? On Jun 7, 3:32=A0pm, "News" <n...@likl.com> wrote: > I use windows server 2008 and formatted it as 2003 functional server mode > and run > active directory domain > > and then I use another windows server 2003 join into the forest as anothe= r > active dir...

In excel, how do you asign numeric values to text or letters, eg..
How can I asign numeric values for text of letters, eg. A=1, B=2, C=3, etc. Hi if you have this kind of order try (letter in cell A1): =CODE(A1)-64 -- Regards Frank Kabel Frankfurt, Germany adefreitas wrote: > How can I asign numeric values for text of letters, eg. A=1, B=2, > C=3, etc. ...

outlook 2000, email editor is word, no room for text
New user of Outlook 2000. Set WORD 2000 as email editor. Open for New mail and get big gray space after first line of tool bar and small space to enter text. Each subsequent new mail, the text space disappears completely as the gray space between the two tool bars expands. Please Help. Thanks, Meryl ...

how do I combine workbooks, adding new text
I have two key contacts spreadsheets with names, addresses etc, which I now want to combine, adding one to the other, not overwriting unless the data is the same. Can I do this in Excel? The only build-in functionality that can do this is Data, Filter, Advanced Filter. This requires that you copy and paste the two lists together to make one list. There should be one row per contact like: Name1 Address1 Name2 Address2 etc. Once you arrange the data this way you can use the above command, selecting the Copy to another location and Unique records only options. Any identical records ...

AUTD text message garbled, device not syncing upon receipt of
I have Exchange 2003 sp2 and a PPC6700 (alltel) that I have been using swimmingly for months now. I have it setup to sync every 10 minutes during peak hours, then use AUTD the rest of the time. Last week, I started getting garbage text messages showing the sender as 6245. I figured out this is the text message that is supposed to tell my device I have a new email and to go get it. The scheduled sync is still working great. I have soft reset the device, and rebooted the exchange server. Any ideas? Thanks! er - you aren't supposed to see the AUTD message at all. It should be forma...

CListCtrl: Custom drawing a row background, but not the text?
Hi, Is it possible to use custom draw so you can draw the row background yourself, but let the control draw the rest? I'd like to show a progress bar behind the entire row but I don't like to draw all text myself. The only way I know is to make the CListCtrl ownerdrawer, override DrawItem and put ALL the necessary code in there, including drawing background and text. There is plenty of sample code knocking around. M "Olaf van der Spek" <OlafvdSpek@GMail.Com> wrote in message news:eoKrBx9rEHA.2196@TK2MSFTNGP15.phx.gbl... > Hi, > > Is it possible to use...

How do I run a report and identify orders that have been added, d.
I have a large amount of data and I want to be able to easily identify changes, additions, and deletions from the report. Hi you need to provide more details: - data layout - what changes can occur - etc. -- Regards Frank Kabel Frankfurt, Germany Cyle wrote: > I have a large amount of data and I want to be able to easily identify > changes, additions, and deletions from the report. ...

Is there any way to make the format (General, Number, Text) of the cells visible?
Is there any way to make the format (General, Number, Text) of the cells visible? I always have to rightclick > Format to check it. Bart Ex2003 Hi Bart There's a way to create a formula to display the number format using an XLM4 macro function: 1. Go Insert>Name>Define and name the formula you are about to create something like NumFmt (you have to do it this 'indirect' way when using XLM4 macros) and in the refers to box type: =GET.CELL(7,OFFSET(INDIRECT("RC",FALSE),0,-1)) and click OK. 2. Now this formula can be used in any worksheets in this workbook a...