Results 1 to 6 of 6
  1. #1
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62

    Why do I have an extra blank string?

    Can someone please look at this and tell me why I'm getting an extra blank string? I *think* it may have something to do with my first For Loop (For I = 1 To N), but not sure. I've gone back and forth with this for the past hour and I'm getting nowhere. It's causing havoc on a working VBA script that takes a string and inserts commas and an 'and' in the proper spot.

    Code:
    Private Sub btnPush_Click()
    
    Dim strText As String, I As Integer, N As Integer
    Dim Activities As Variant
    
    
    strText = " "
    N = 6
    
    
    For I = 1 To N
        'If Me("cb1") = True Then strText = strText & Me("Action1")
        'Next
        If Me("cb" & I) = True Then strText = strText & Me("Action" & I) & ";"
    Next
    
    
    MsgBox strText
    
    
    Dim myActivities() As String
    
    
    'ADDING MY COMMAS/AND CODE
    'Split the string into a dynamic array
    myActivities() = Split(strText, ";")
    Activities = Join(myActivities, ",")
    
    
    For z = LBound(myActivities()) To UBound(myActivities())
        MsgBox myActivities(z)
    Next
    MsgBox LBound(myActivities())
    MsgBox UBound(myActivities())
    
    
    Dim result As String
    For x = 0 To UBound(myActivities())
        If UBound(myActivities()) = 0 Then
            result = myActivities(0) & "."
        ElseIf UBound(myActivities()) = 1 Then
            result = myActivities(0) & " and " & myActivities(1) & "."
        Else
            If x = UBound(myActivities()) Then
                result = result & myActivities(x) & "."
            ElseIf x = (UBound(myActivities()) - 1) Then
                result = result & myActivities(x) & ", and "
            Else
                result = result & myActivities(x) & ", "
            End If
        End If
    Next
    
    
    MsgBox result
    
    Me.Sentence.Value = Name & " " & result
    
    
    
    End Sub
    Thanks!

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Where is the extra blank line? At the start or end?
    Suggest you post the result you get
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    Quote Originally Posted by ridders52 View Post
    Where is the extra blank line? At the start or end?
    Suggest you post the result you get
    My apologies. It was inserting a blank string at the end. Thus I would have something like "wakes up,gets dressed,<null value>".

    I fixed it though (granted, its a work-around) by subtracting 1 from the UBound(myArray).

    Code:
    Private Sub btnPush_Click()
    
    Dim strText As String, I As Integer, N As Integer
    Dim Activities As Variant
    
    
    strText = ""
    N = 6
    
    
    For I = 1 To N
        If Me("cb" & I) = True Then strText = strText & Me("Action" & I) & ";"
    Next
    
    
    MsgBox strText
    
    
    Dim myActivities() As String
    
    
    myActivities() = Split(strText, ";")
    Activities = Join(myActivities, ",")
    
    
    For z = LBound(myActivities()) To UBound(myActivities())
        MsgBox myActivities(z)
    Next
    MsgBox LBound(myActivities())
    MsgBox UBound(myActivities())
    Dim thisThing As String
    thisThing = UBound(myActivities()) - 1
    MsgBox "This Thing = " & thisThing
    
    
    Dim result As String
    For x = 0 To thisThing
        If thisThing = 0 Then
            result = myActivities(0) & "."
        ElseIf thisThing = 1 Then
            result = myActivities(0) & " and " & myActivities(1) & "."
        Else
            If x = thisThing Then
                result = result & myActivities(x) & "."
            ElseIf x = (thisThing - 1) Then
                result = result & myActivities(x) & ", and "
            Else
                result = result & myActivities(x) & ", "
            End If
        End If
    Next
    
    
    MsgBox result
    
    
    Me.Sentence.Value = myName & " " & result
    
    
    End Sub
    Click image for larger version. 

Name:	Snap1.png 
Views:	18 
Size:	14.8 KB 
ID:	33216

    If there is a suggestion on a better solution though, I'm all for it. Would rather be rid of workarounds, if possible.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Arrays typically begin with 0 (zero) as the LBound. If you want the lower bound to begin with 1, then you must add the command
    Option Base 1

    Your code would look like
    Code:
    Option Compare Database
    Option Explicit
    Option Base 1
    
    Private Sub btnPush_Click()
    
    'rest of the code
    You also have 2 undeclared variables in your code: "x" and "z".
    I would add the "Me." keyword in front of "MyName" (Me.MyName) in the last line.


    I rewrote your code to not use an array.
    Code:
    Option Compare Database   '<<-- should be at the top of EVERY code module
    Option Explicit           '<<-- should be at the top of EVERY code module
    
    Private Sub btnPush_Click()
    
        Dim strText As String
        Dim I As Integer
        Dim N As Integer
        
        strText = ""
        N = 5
    
        'get selected actions
        For I = 1 To N
            If Me("cb" & I) = True Then
                strText = strText & Me("Action" & I) & ";"
            End If
        Next
        ' MsgBox strText
    
        'replace semicolon with comma and space
        strText = Replace(strText, ";", ", ")
        ' MsgBox strText
    
        'remove last comma & space and add a period
        strText = Left(strText, Len(strText) - 2) & "."
        ' MsgBox strText
    
        Me.Sentence = Me.MyName & " " & strText
    End Sub

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    cb1 ... cb5 is somewhat confusing for checkbox name! Better rename them as chk1 ... chk5
    Code:
    ...
    Dim intCnt As Integer
    ...
        intCnt = 0
        For I = N To 1 -1
            If Me("cb" & I) = True Then
                If Nz(Me("Action" & I),"") = "" Then
                ElseIf Nz(strText,"") = "" AND Nz(Me("Action" & I),"") <> ""Then
                    inCnt = intCnt +1
                    strText = Me("Action" & I) & "."
                ElseIf Nz(Me("Action" & I),"") <> "" And intCnt = 1 Then
                    inCnt = intCnt +1
                    strText = Me("Action" & I)  & " and " & strText
                ElseIf Nz(Me("Action" & I),"") <> "" AND intCnt > 1 Then
                    strText =Me("Action" & I) & ", " &  strText
                End If
            End If
        Next N
    ...
    No further editing of string is needed.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    database99.zip
    Here's another approach using collections.
    colAlpha is static and holds the individual activity strings.
    colIndex (after making the checkbox selections) holds a number indicating the position of the activity.
    colSorted holds the contents of colIndex sorted.
    The concatenated Activity string is built by iterating thru colSorted and extracting the corresponding activity position from colAlpaha. This keeps the order the same as in colAlpha, no matter the order selected with the checkboxes.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 01-31-2018, 04:06 PM
  2. Replies: 6
    Last Post: 11-17-2015, 09:16 AM
  3. Importing Excel File - Getting 7 extra blank rows each import
    By eking002 in forum Import/Export Data
    Replies: 4
    Last Post: 06-13-2013, 09:15 AM
  4. Extra Blank Page in Report
    By Ellpee in forum Reports
    Replies: 6
    Last Post: 05-15-2013, 04:08 PM
  5. Extra data
    By newtoAccess in forum Reports
    Replies: 14
    Last Post: 11-28-2010, 12:05 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums