Results 1 to 14 of 14
  1. #1
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54

    #Size! error on function in textbox

    I have a textbox that should be generated from a set of dates in a subform. This function returns #size! error in the textbox. Hoping someone can help me decipher what the problem is.



    Code:
    Public Function DatesToTextBox() As String 'returns string value of dates in recordset, comma separated, formatted 'm/d'
        Dim v As Variant
        Dim s As String
        '
        '
        '
        With Me!subfrmIODates.Form.RecordsetClone
            If Not (.BOF And .EOF) Then .MoveFirst
            While Not .EOF
                v = .Fields("Air_Date").value
                If Not IsNull(v) Then
                    v = Split(v, " ")(1)
                    v = CDate(v)
                    s = s & Format(v, "m\/d") & ", "
                End If
                Debug.Print s
                .MoveNext
            Wend
        End With
        If Len(s) > 0 Then s = Left(s, Len(s) - 2)
        DatesToTextBox = s
        
    End Function
    Thanks in advance for your great advice!
    MIB1019

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What does the debug produce? How are you calling the function?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    txtDatesSelected = DatesToTextbox()

    Did a Debug.Print s and got nothing.

    Thanks!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd set a breakpoint and step through the code line by line, so you can see what values are coming from the form and how the code is handling them. What does the field contain?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you show us some sample data from .Fields("Air_Date"). Is Air_Date the field name in the subform's record source? Also try to initialize the s (string variable before entering the While loop (s="").

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    Okay, I did some print.debug statements and I think it's in the split function. Any ideas?

    Code:
    Public Function DatesToTextBox() As String 'returns string value of dates in recordset, comma separated, formatted 'm/d'
        Dim v As Variant
        Dim s As String
        '
        '
        '
        With Me!subfrmIODates.Form.RecordsetClone
            If Not (.BOF And .EOF) Then .MoveFirst
            Debug.Print "moving" GOT THIS MESSAGE
            While Not .EOF
                v = .Fields("Air_Date").value
                Debug.Print v GOT VALUE OF FIRST RECORD ON SUBFORM
                If Not IsNull(v) Then
                Debug.Print "v has value" GOT THIS MESSAGE
                    v = Split(v, " ")(1)
                    Debug.Print v NOTHING
                    v = CDate(v)
                    Debug.Print v NOTHING
                    s = s & Format(v, "m\/d") & ", "
                    Debug.Print s NOTHING
                End If
                .MoveNext
            Wend
        End With
        If Len(s) > 0 Then s = Left(s, Len(s) - 2)
        DatesToTextBox = s
        
    End Function

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Why use Split if you have only one date in the field (for each record)? Try:
    Code:
    Public Function DatesToTextBox() As String 'returns string value of dates in recordset, comma separated, formatted 'm/d'
        Dim v As Variant
        Dim s As String
        '
        '
        '
        With Me!subfrmIODates.Form.RecordsetClone
            If Not (.BOF And .EOF) Then .MoveFirst
            Debug.Print "moving" GOT THIS MESSAGE
            s=""
    	
            While Not .EOF
                v = .Fields("Air_Date").value
                Debug.Print v GOT VALUE OF FIRST RECORD ON SUBFORM
                If Not IsNull(v) Then
                Debug.Print "v has value" GOT THIS MESSAGE
                    'v = Split(v, " ")(1)
                    'Debug.Print v NOTHING
                    v = CDate(v)
                    'Debug.Print v NOTHING
                    s = s & Format(v, "m\/d") & ", "
                    Debug.Print s NOTHING
                End If
                .MoveNext
            Wend
        End With
        If Len(s) > 0 Then s = Left(s, Len(s) - 2)
        DatesToTextBox = s
        
    End Function
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    We've both asked what the value in the field is. What does the debug show for v?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    On the particular set of subform records, there were 6 dates, so the recordset would hold
    07/02/2020
    07/07/2020
    07/09/2020
    07/14/2020
    07/16/2020
    7/21/2020

    The purpose of the function is to split all these values up into a the text box, so it should calculate to: ‘7/2, 7/7, 7/9, 7/14, 7/16, 7/21’.
    The first record value 07/02/2020, is returned on debug on v =.fields(“Air_Date”).value. And not again as that first v value gets to split.

    I hope that answers the question.
    MIB1019

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You're splitting on a space, and no space exists. You're using (1) for the array value, 0 would be the first value. 1 will error since there is no second value. Drop the Split function and just use the Format() function directly on v if it's a date/time data type, or after applying CDate() if it's a text data type.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Just as I suggested in my previous post, have you tried it?

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    Will try it this morning, and let you know. Thanks for the advice!
    MIB1019

  13. #13
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    Totally makes sense that split function didn't belong. I took it out and the function works perfectly!

    Thanks for the help. I'm always learning!
    MIB1019

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're very welcome, good luck with your project!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. #SIZE! What am I doing wrong? (NZ function)
    By Gina Maylone in forum Access
    Replies: 4
    Last Post: 12-16-2015, 10:22 AM
  2. AutoFit Text size to display in a form textbox
    By QA_Compliance_Advisor in forum Programming
    Replies: 6
    Last Post: 08-08-2015, 05:00 PM
  3. Replies: 2
    Last Post: 04-20-2013, 03:37 AM
  4. Size a textbox
    By NISMOJim in forum Forms
    Replies: 3
    Last Post: 06-17-2011, 06:20 PM
  5. Replies: 1
    Last Post: 01-14-2010, 04:10 PM

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