Build comma separated string

[MDA]

Hi all,

I have six text boxes.. I need to build a comma separated string from the six boxes. The trick is that it is possible to not use all six boxes. My issue is that I end up with extra comma's if not all boxes are used. I currently have the following:

----------------------
segString = seg1.Text & ", " & seg2.Text & ", " & seg3.Text & ", " & seg4.Text & ", " & seg5.Text & ", " & seg6.Text
----------------------

I need to either remove the commas if there is more then one near each other, or build the string differently.

Any ideas are greatly appreciated.

MDA

 

[SonOfEmidec1100]

Is this what you want - bit cumbersome but should do the job

segstring = seg5.Text & IIf(seg5.Text <> &quot;&quot; And seg6.Text <> &quot;&quot;, &quot;, &quot;, &quot;&quot & seg6.Text
segstring = seg4.Text & IIf(seg4.Text <> &quot;&quot; And segstring <> &quot;&quot;, &quot;, &quot;, &quot;&quot & segstring
segstring = seg3.Text & IIf(seg3.Text <> &quot;&quot; And segstring <> &quot;&quot;, &quot;, &quot;, &quot;&quot & segstring
segstring = seg2.Text & IIf(seg2.Text <> &quot;&quot; And segstring <> &quot;&quot;, &quot;, &quot;, &quot;&quot & segstring
segstring = seg1.Text & IIf(seg1.Text <> &quot;&quot; And segstring <> &quot;&quot;, &quot;, &quot;, &quot;&quot & segstring

 

[johnwm]

Just loop through looking for &quot;,,&quot; and replace.

Something like:
Do While Instr(myStr,&quot;,,&quot > 0 'If there is &quot;,,&quot; in the string
Replace (myStr,&quot;,,&quot;,&quot;,&quot 'change it to &quot;,&quot;
Loop 'Until you've done

This is untested and has no error checking, but should get you started


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[MichaelRed]

On the other side, why do you want to remove the field seperators? Most uses of CSV files expect a constant number of 'fields' and know quite well that the consecutative seperators simply mean no info in the field.

In the general case, receieving the CSV string with less than the expected (5?) number of seperators leads to a bit of confusion as to WHAT to do - or which 'fields' are present / absent?



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MDA]

Thanks for the input guys...

In response to MichaelRed, I am actually trying to build a query statement so two commas would fail. I am trying to build sort of a drag and drop query. For instance there are six fields the user can choose from but they may not want all six. So they drag the fields or (columns) they want included in the query to different text boxes and that creates the 'SELECT' statement. If there are extra commas the SELECT statement will fail.

Thanks again, I will try both methods above.

Regards,
MDA

 

[MichaelRed]

O.K. it SORTA makes sense ... but ... but ... then if you have the &quot;sources&quot; text box or combo box couldn't the builder reference them as easily as the construction of the string object?

I would ASSUME you are including fields from a recordset in some manner on a form (&quot;Drag and Drop&quot. This has been discussed a bit in these fora, and (I believe) reasonably well 'documented' through the use of forms based selection of fields w/o the intermediate creation of the CSV srtin (w/ surplus delimiters). I recall (but recall at my age is faullty) the control of choice is a ComboBox with MultiSelect. Just loop through and build the fields directly from the &quot;Selected&quot; items.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MDA]

Granted I am a VB novice, so my approach may not be up to par... However, I think what I am doing is more complex then first glance.

FYI: Basically the query is actually creating a mapping from one DB to an accounting system. In the accounting package the account is made up of several segments such as an entity, department, product, project, etc. It would look something like xxxx-xxx-xxx-xxx, etc… So in my list box I pull in all the segments from one database (not the accounting DB) and then the user drags and drops the segments into the correct segment corresponding to their account string. So I don’t necessarily know the order that the segments exist in their account string.

So in this case, a combo box would not work as I need to make sure the order is correct. For example, one company may have the department as segment1 but another company may have the department as segment2. The user would drag the field called DEPT from the list box into the appropriate segment text box. Perhaps there is a better way of doing this? Any ideas?

Thanks again for your feedback.. It is always nice to hear thoughts from a veteran.

Regards,
MDA

 

[Federal102]

If you have six text boxes, some of which could be empty, you could try something like this...

Dim myArray(6) As String

myArray(0) = Text1.Text
myArray(2) = Text2.Text
myArray(3) = Text3.Text
myArray(4) = Text4.Text
myArray(5) = Text5.Text
myArray(6) = Text6.Text
Dim segString As String
For ctr = 0 To UBound(myArray)
  If myArray(ctr) <> vbNullString Then
     segString = segString + myArray(ctr) + &quot;,&quot;
  End If
Next ctr

segString = Mid(segString, 1, Len(segString) - 1)

 

[MichaelRed]

I would (AGAIN!) 'guess' that for a given company the account code would have the 'same' configuration.

IF (and it is a CAPATIALIZED IF) such is true, then a 'documented' approach is ofer refered to as &quot;cascading combo boxes&quot;. In such a scenario, on current of the form would have ONLY the company combo box enabled, and set focus on it. When (if?) a selection is made here, the 'format' of the account number string becomes known, so the combobox for that portion os the account number is unlocked and focus is set to that. On selection here, the next segment is enabled. When all the relevant segments have been selected, you can build the appropiate string - without the extraneous commas. Since (again &quot;GUESSING&quot the Company determines the segment order, a simple 'rule' set based on the company should easily set the order to enable the comboboxes and to assemble them into the appropiate string.

In permitting 'corrections' or changes of 'direction', you should keep track of (i.e. use the change events) of hte alread filled combo boxes and &quot;blank&quot; ones which follow the one changed, and re-set the segment 'guide'/counter/pointer to the one changed as well as locking the ones following 'others'.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places