Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List Boxes (VB6)

    I am something of a newcomer to VB and have a problem with which I am struggling unsuccessfully.

    I am displaying data in a list box and the code I have, in essence, is:
    With Data1.Recordset
    .MoveFirst
    Do Until .EOF
    lstDisplay.AddItem !DDAY & "/" & !DMONTH & "/" & !DYEAR _
    & Chr$(9) & !NAME1 & " & " & !NAME2
    .MoveNext
    Loop
    End With

    This works fine but I want to replace the " & " between NAME1 and NAME2 with a variable so that if
    either name is missing the " & " is omitted. So far, I have tried all combinations of " and ' round the
    variable name that I can think of but all give a compiler error.

    Would somebody please enlighten me of the correct syntax.

    johncp

  2. #2
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: List Boxes (VB6)

    Try this:

    Dim myAnd as String

    With Data1.RecordSet
    .MoveFirst
    Do until .EOF
    myAnd = "&"
    If !NAME1 = vbNullString Or !NAME2 = vbNullString Then
    myAnd=vbNullString
    Endif
    lstDisplay.AddItem !DDAY & "/" & !DMONTH & "/" & !DYEAR _
    & Chr$(9) & !NAME1 & myAnd & !NAME2
    .MoveNext
    Loop
    End With

    Regards,
    Kevin Bell

  3. #3
    New Lounger
    Join Date
    Mar 2004
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Boxes (VB6)

    Thanks for the response. Your syntax was not thrown out by the compiler - good news but didn't work - bad news.
    I then tried:
    dim strName1 as string
    dim strName2 as string
    If !NAME1 <> vbNullString Then
    strName1 = !NAME1
    Else
    strName1 = vbNullString
    End If
    If !NAME2<> vbNullString Then
    strName2 = !NAME2
    Else
    strName2 = vbNullString
    End If
    If strName1 = vbNullString Or strName2 = vbNullString Then
    strAmp = vbNullString
    Else
    strAmp = " & "
    End If

    This extra code works fine. Don't ask me why!!

  4. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: List Boxes (VB6)

    I use a custom rolled function to perform this sort of task where s1 is the initial string, s2 is the secondary string and s3 is used in the middle if both s1 and s2 have content.

    Public Function funConcat(s1 As String, s2 As String, s3 As String) As String
    funConcat = IIf(s1 = "", s2, IIf(s2 = "", s1, s1 & s3 & s2))
    End Function

    If you pass it three string variables it will join all three (s1 + s3 + s2) but if either s1 or s2 are empty then the other is displayed without the s3 separator
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  5. #5
    New Lounger
    Join Date
    Mar 2004
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Boxes (VB6)

    Tried the funConcat solution as it looked an elegant answer. However, received Error 94, Invalid use of Null when it hit the first Null.

    This was the same before until I put the If statements and new variables in to check for Nulls

  6. #6
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: List Boxes (VB6)

    You can try something like this...
    '--------------------------------------------
    Sub CustomStrings()
    Dim strChar As String
    Dim Name1 As String
    Dim Name2 As String

    Name1 = "first"
    Name2 = "second"
    If Len(Name1) * Len(Name2) > 0 Then strChar = "&"
    MsgBox strChar
    End Sub
    '------------------------------------

    Jim Cone
    San Francisco, CA

  7. #7
    New Lounger
    Join Date
    Mar 2004
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Boxes (VB6)

    Tried this, thanks. It would appear that the length of a Null is Null and that when one Null and a number , or two Nulls, are multiplied the result is Null or at least greater than 0. I regret to say then, failure with this proposed solution.
    I find VB treatment of Null bizarre. I still don't know why my solution to this problem works (see earlier post)

  8. #8
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: List Boxes (VB6)

    I think because you are dealing with database values you have to check for the value beaing Null, not vbNullString.

    So this SHOULD work (and I emphasise the SHOULD)

    Dim sName1 as String, sName2 as String, sAnd as String

    With Data1.Recordset
    .MoveFirst
    Do Until .EOF
    If IsNull(!Name1) Then
    sName1=vbNullString
    Else
    sName1=!Name1
    Endif
    If IsNull(!Name2) Then
    sName2=vbNullString
    Else
    sName2=!Name2
    Endif
    sAnd="&"
    If sName1 = vbNullString Or sName2 = vbNullString then
    sAnd=vbNullString
    Endif
    lstDisplay.AddItem !DDAY & "/" & !DMONTH & "/" & !DYEAR _
    & Chr$(9) & sName1 & sAnd & sName2
    .MoveNext
    Loop
    End With

    Regards,
    Kevin Bell

  9. #9
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: List Boxes (VB6)

    Does this cure it - merging BigKev's method with a modified funConcat
    <pre>Sub BeatsMe()
    Dim sName1 As String, sName2 As String, sAnd As String, sNameCombo As String
    With Data1.Recordset
    .MoveFirst
    Do Until .EOF
    sNameCombo = funConcat2(!Name1, !Name2, " & ")
    lstDisplay.AddItem !DDAY & "/" & !DMONTH & "/" & !DYEAR _
    & Chr$(9) & sNameCombo
    .MoveNext
    Loop
    End With
    End Sub
    Function funConcat2(sName1 As String, sName2 As String, sAnd As String) as String
    If IsNull(sName1) Then sName1 = ""
    If IsNull(sName2) Then sName2 = ""
    funConcat2 = IIf(sName1="", sName2, IIf(sName2="", sName1, sName1 & sAnd & sName2))
    End Function</pre>

    Andrew Lockton, Chrysalis Design, Melbourne Australia

  10. #10
    New Lounger
    Join Date
    Mar 2004
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Boxes (VB6)

    Thanks, yes this does work. I assume that post 355517 from Andrew will also work as it looks the same.

    Thanks to all for their assistance with my first venture into Wood's Lounge.

    johncp <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •