Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    115

    Unanswered: Max number of characters on function NameString

    I used the function bellow for about 2 years and never had a problem until I
    got to a point where this function combined about 9 people and reached more
    then 255 characters, and report does not display all names.

    Your help is very appreciated.

    Thank you

    Function NameString(RecordID As Variant, TableName As String, _
    FieldName As String) As String

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim AssignmentID As Long
    Dim FirstName As String
    Dim MiddleName As String
    Dim LastName As String
    Dim Suffix As String
    Dim Add As String
    Dim NameList As String

    NameList = ""

    RecordID = Nz(RecordID, 0)
    strSQL = "SELECT * FROM [" & TableName & _
    "] WHERE [" & FieldName & "] = " & RecordID & ";"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)

    If rs.RecordCount > 0 Then
    Do While Not rs.EOF '<<
    ' NameList = ""
    FirstName = Nz(rs(1) + " ", "")
    MiddleName = Nz(rs(2) + " ", "")
    LastName = Nz(rs(3) + " ", "")
    Suffix = Nz(rs(4) + " ", "")
    Add = Nz(rs(5) + " ", "")

    NameList = NameList & FirstName & MiddleName & _
    LastName & Suffix & Add & " "
    'NameList = Trim(NameList)
    rs.MoveNext
    Loop
    End If

    NameString = NameList
    End Function

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Does changing Namestring return to a variant help? Otherwise, could you throw it into another variant/string or maybe put it into a temporary memo type field or possibly combine them or have 2 fields on the report which are next to each other?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2004
    Posts
    115
    FYI

    The Query looks fine. It only happens in the report.

    In addition, I use the same function for other reports, and have no problem with that reports. Actually, that reports are being used as sub reports for this report.

    The report/query has several fields that use the same function

    Thanks

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Is the field on your report set to allow it to grow?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jan 2004
    Posts
    115
    Fyi,

    Yes

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Any chance of perhaps creating a table with these names and somehow put a subreport (creativity on paragrahing) in your main report? It's a little more work but it might work. Other than that, I'm not quite sure...need to think on it for a bit.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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