Increasing Character length

[mans]

Hello,

I am using VB6 with Access2000. I am running the code below to allow the addition of new records to a table. I click in check boxes within a List Box (List1) adjacent to Clients (people) and then click a command to run the code below. I cannot add more than one record at a time if the Client ID is greater than 6 characters, otherwise I get a data conversion error with respect to the Payment field below (currency data type). Can somebody please let me know what I can do to the code below to allow the addition of the Client_ID irrespective of the number of Client_ID characters or at least greater than 6. Please ignore declarations and assignments etc.

The format of the list box (List1) is as follows:
List1.AddItem rj!Client_ID & " " & rj!Surname & ", " & rj!FirstName & " " & rj!payment


For I = 0 To List1.ListCount – 1
If List1.Selected(I) Then
rj.AddNew
List1.ListIndex = I
Col1 = InStr(List1.Text, "&quot
Col2 = InStr(Col1, List1.Text, ",&quot

rj!Client_ID = Left(List1.Text, InStr(List1.Text, " &quot - 1)
rj!payment = Mid(List1.Text, Len(List1.Text) - InStr(List1.Text, " &quot)


rj.Update
End If
Next

Thank You
[sig][/sig]

 

[swilliams]

In the line:

rj!payment = Mid(List1.Text, Len(List1.Text) - InStr(List1.Text, " &quot)

the 'InStr(List1.Text, " &quot' part will look for the first occurrence of a space in the string List1.Text. This is found after the client id in the string made from

rj!Client_ID & " " & rj!Surname & ", " & rj!FirstName & " " & rj!payment

so Len...-InStr... will not necessarily find the correct currency amount.

Instead of rj!payment = Mid.... try using:

rj!payment = Right(List1.Text, Len(List1.Text) - InStr(List1.Text, " &quot - 1)
[sig]<p>Simon<br>[/sig]

 

[swilliams]

PS from last post.

Notice that the InStr looks for two consecutive spaces.
Right.... will return the spaces. I don't think this will be a problem when assigning to a currency field, but if it is, do

rj!payment = Trim(Right(..)) [sig]<p>Simon<br>[/sig]