Remove comma from first string?

[topdesk123]

Hello all!

This is a sample of the code I'm using to produce a sentence that includes the items that customer's order. The first line will change depending on the order as well. I just need to know how to keep it from inserting a comma before the first item.

My thanks to Cajun Centurion for this solution months ago!

TIA!


Dim lCol_IncludeAreas As New Collection
Dim lStr_FullSent As String
Dim lInt_Idx As Integer


If (Me![Sealer] = True) Then
lCol_IncludeAreas.Add "Company will do this "
End If

If (Me![item1] = True) Then
lCol_IncludeAreas.Add "item1sentence"
End If

If (Me![item2] = True) Then
lCol_IncludeAreas.Add "item2sentence"
End If

If (Me![item3] = True) Then
lCol_IncludeAreas.Add "item3sentence"
End If


Select Case lCol_IncludeAreas.Count
Case 0
lStr_FullSent = vbNullString
Case 1
lStr_FullSent = lCol_IncludeAreas.Item(1)
Case 2
lStr_FullSent = lCol_IncludeAreas.Item(1) & " and " & lCol_IncludeAreas.Item(2)
Case Else
lStr_FullSent = vbNullString
For lInt_Idx = 1 To lCol_IncludeAreas.Count - 1
lStr_FullSent = lStr_FullSent & lCol_IncludeAreas.Item(lInt_Idx) & ", "
Next lInt_Idx
lStr_FullSent = lStr_FullSent & "and " & lCol_IncludeAreas.Item(lCol_IncludeAreas.Count)
End Select

Me![Sentence] = "" & lStr_FullSent & " for " & (FormatCurrency(Forms![bid sheet]![ActItem]) & ". ")


Exit_Command59_Click:
Exit Sub

Err_Command59_Click:
MsgBox Err.Description
Resume Exit_Command59_Click
End Sub

 

[lespaul]

I usually do something like this (there may be a better way, but if so, I don't know it!!):

 Case Else
   lStr_FullSent = vbNullString
   For lInt_Idx = 1 To lCol_IncludeAreas.Count - 1
     [b]if lStr_FullSent = '' then
        lStr_FullSent = lCol_IncludeAreas.Item(lInt_Idx)
     else
        lStr_FullSent = lStr_FullSent & lCol_IncludeAreas.Item(lInt_Idx) & ", "[/b]
   Next lInt_Idx

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[topdesk123]

Thank you Leslie! I'm getting syntax error on:

if lStr_FullSent = '' then

Any thoughts?

THANKS AGAIN!

 

[lespaul]

I think it's because you have set lStr_FullSent to vbNullString, you would probably have to check if lStr_FullSent is NULL, but I didn't know the syntax for that (I usually program in Delphi and it's different syntax!)


Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[topdesk123]

Ok, got that problem fixed and now I'm getting the error: Case without For

Case Else
lStr_FullSent = vbNullString
For lInt_Idx = 1 To lCol_IncludeAreas.Count - 1
If lStr_FullSent = Null Then
lStr_FullSent = lCol_IncludeAreas.Item(lInt_Idx)
Else
lStr_FullSent = lStr_FullSent & lCol_IncludeAreas.Item(lInt_Idx) & ", "
Next lInt_Idx

I appreciate your help - I really don't get some of this stuff!!

 

[lespaul]

Maybe a missing END IF?

Case Else
   lStr_FullSent = vbNullString
   For lInt_Idx = 1 To lCol_IncludeAreas.Count - 1
     If lStr_FullSent = Null Then
        lStr_FullSent = lCol_IncludeAreas.Item(lInt_Idx)
     Else
        lStr_FullSent = lStr_FullSent & lCol_IncludeAreas.Item(lInt_Idx) & ", "
     [b]End If[/b]
   Next lInt_Idx

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[topdesk123]

Thanks again Leslie! I can run it without errors now BUT there is a comma after my first line ("Company will do, this, this and this)

If I remove the comma from the Else statement, the no commas appear whatsoever.

Case Else
lStr_FullSent = vbNullString
For lInt_Idx = 1 To lCol_IncludeAreas.Count - 1
If lStr_FullSent = Null Then
lStr_FullSent = lCol_IncludeAreas.Item(lInt_Idx)
Else
lStr_FullSent = lStr_FullSent & lCol_IncludeAreas.Item(lInt_Idx) & ", "
End If
Next lInt_Idx
End Select

 

[lespaul]

So the first time through you add:

Company will do

then you add the second value (which you don't want a comma before)

Company will do 1st thing

then you add the third value (and you do want a comma before it)

Company will do 1st thing, 2nd thing

right?

Case Else
   lStr_FullSent = vbNullString
   For lInt_Idx = 1 To lCol_IncludeAreas.Count - 1
     If lStr_FullSent = Null OR lStr_FullSent = "Company will do" Then
        lStr_FullSent = lStr_FullSent  & " " & lCol_IncludeAreas.Item(lInt_Idx)
     Else
        lStr_FullSent = lStr_FullSent & lCol_IncludeAreas.Item(lInt_Idx) & ", "
     End If
   Next lInt_Idx
   End Select

you may end up with an extra " " so you may need to trim that.

les

 

[PHV]

You wanted something like this ?
Select Case lCol_IncludeAreas.Count
Case 0
lStr_FullSent = vbNullString
Case 1
lStr_FullSent = lCol_IncludeAreas.Item(1)
Case Else
lStr_FullSent = vbNullString
For lInt_Idx = 1 To lCol_IncludeAreas.Count - 1
lStr_FullSent = ", " & lStr_FullSent & lCol_IncludeAreas.Item(lInt_Idx)
Next lInt_Idx
lStr_FullSent = Mid(lStr_FullSent, 3) & " and " & lCol_IncludeAreas.Item(lCol_IncludeAreas.Count)
End Select

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[topdesk123]

Thank you both (again) for your help. The following works great except I am still getting a comma at the end of the string:

Select Case lCol_IncludeAreas.Count
Case 0
lStr_FullSent = vbNullString
Case 1
lStr_FullSent = lCol_IncludeAreas.Item(1)
Case 2
lStr_FullSent = lCol_IncludeAreas.Item(1) & " " & lCol_IncludeAreas.Item(2)
Case Else
lStr_FullSent = vbNullString

For lInt_Idx = 1 To lCol_IncludeAreas.Count - 1
If lStr_FullSent = Null Or lStr_FullSent = " Company will strip, clean, brighten and seal/stain the " Then
lStr_FullSent = lStr_FullSent & " " & lCol_IncludeAreas.Item(lInt_Idx)
Else
lStr_FullSent = lStr_FullSent & lCol_IncludeAreas.Item(lInt_Idx) & ", "
End If
Next lInt_Idx
lStr_FullSent = lStr_FullSent & "and " & lCol_IncludeAreas.Item(lCol_IncludeAreas.Count)
End Select


Me![Sentence] = "" & lStr_FullSent & " for " & (FormatCurrency(Forms![bid sheet]![ActPrice]) & ". ")

Produces this:
Company will strip, clean, brighten and seal/stain the, walking surface of your deck, railings, spindles, and steps for $668.00.

I know I'm missing something blatantly obvious!