Results 1 to 4 of 4

Thread: Sytax help

  1. #1
    Join Date
    Feb 2004
    Location
    London
    Posts
    46

    Exclamation Unanswered: Sytax help

    Hiya,

    I'm trying to display days of the week from list box. As the end result goes into a mail merge I need to write "and" and "," in..

    for example if 2 days of the week are selected it does

    Monday and Wednesday

    3 would be

    monday, Tuesday and Wednesday

    etc... anyway i've composed a way of selecting them though I can't put the "and" and "," in between, I just get:

    mondaytuesdaywednesday

    here's the code I'm using where txtselected displays the results and txt7 is the amount of results selected:

    Dim varItem As Variant
    Dim strList As String

    With Me!lstCompanies
    If .MultiSelect = 0 Then
    Me!txtSelected = .Value

    Else

    If Text7.Value = "1" Then
    For Each varItem In .ItemsSelected
    strList = strList & .Column(0, varItem)
    Next varItem
    Me!txtSelected = strList
    End If

    If Text7.Value = "2" Then
    For Each varItem In .ItemsSelected
    strList = strList & .Column(0, varItem)
    Next varItem
    Me!txtSelected = strList

    much thanks!
    Last edited by Andyp; 03-15-04 at 07:38.

  2. #2
    Join Date
    Feb 2004
    Location
    London
    Posts
    46
    sorry make it a little clearer, I need the sytax of putting extra characters in the

    Column(0, varItem)

    if it worked.. something like Column(0, varItem & "and")

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if
    If Text7.Value = "1" Then
    For Each varItem In .ItemsSelected
    strList = strList & .Column(0, varItem)
    Next varItem
    Me!txtSelected = strList
    End If
    does a mondaytuesdaywednesday for you, then

    If Text7.Value = "1" Then
    For Each varItem In .ItemsSelected
    strList = strList & .Column(0, varItem) & " and "
    Next varItem
    strList = left$(strList, len(strList) -5)
    Me!txtSelected = strList
    End If
    will do monday and tuesday and wednesday

    and
    dim stillLooking as boolean
    dim aPointer as integer
    dim lastComma as integer
    dim lastDay as string
    If Text7.Value = "1" Then
    For Each varItem In .ItemsSelected
    strList = strList & .Column(0, varItem) & ", "
    Next varItem
    strList = left$(strList, len(strList) -2)
    lastComma = 0
    stillLooking = true
    do while stillLooking
    aPointer = instr(lastComma + 1, strList, ",")
    if aPointer > 0 then
    lastComma = aPointer
    else
    stillLooking = false
    endif
    loop
    if lastComma = 0 then
    'only one day found - nothing to do
    else
    lastDay = mid$(strList, lastComma +1)
    strList = left$(strList, lastComma - 1)
    strList = strList & " and " & lastDay
    endif
    Me!txtSelected = strList
    End If
    will do a monday, tuesday and wednesday

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Feb 2004
    Location
    London
    Posts
    46
    Izy you're a total superstar!!! Worked first time!!

    I'm always worried if people don't get what you mean, but spot on with that! Spent all dam day trying to work this one out... gonna study that tomorrow and see how it all works ))

    If you're even in the UK I'll buy ya a drink for that

Posting Permissions

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