Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2016
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Commas Between Data

    Hi!
    I'm having trouble with something that's probably really simple to many of you. I've attached an easy small database hoping someone could help me tweak my report so that the numbers on it print separated by commas going across the page instead of printing on individual rows.


    I want it to look like this:
    4776, 4777, 4778, 4779, 4780, 4781

    Instead of:
    4776
    4777
    4778
    4779
    4780
    4781
    Attached Files Attached Files

  2. #2
    WS Lounge VIP Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    1,100
    Thanks
    256
    Thanked 172 Times in 148 Posts
    I'm a novice at Access, but fwiw my thought is you need a calculated field which concatenates the numbers & commas—I don't know if it's better to do that in the table or query, my guess is in the query.
    Lugh.
    ~
    Dell Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
    i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 2 x 256G SSD, 4TB HD

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    San Francisco Bay Area
    Posts
    6
    Thanks
    0
    Thanked 1 Time in 1 Post
    Martin - First of all, your choice of table and field names is terrible. Never use special characters like "#" and avoid spaces. Use underscores instead.

    That said, your request does not have a simple solution, but this function will kind of work. It needs refinement to apply to a report. I've taken your database and made the necessary changes. I added a module named Module1 and wrote a function to concatenate the box numbers based on the Company ID. The attachment here is the new database.


    Public Function ConcatField(strIn As String) As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strOut As String

    Set db = CurrentDb
    strSQL = "SELECT * FROM [## Entry Log] " _
    & "WHERE [Company ID] = '" & strIn & "'"

    'Open recordset on query used for report
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

    With rs
    .MoveLast
    .MoveFirst
    'Loop through records and concatenate box numbers
    Do While Not .EOF
    strOut = strOut & rs.Fields("BOXES") & ","
    .MoveNext
    Loop
    End With

    If Right(strOut, 1) = "," Then
    strOut = Left(strOut, Len(strOut) - 1)
    End If

    ConcatField = strOut

    Set rs = Nothing
    Set db = Nothing
    End Function
    Attached Files Attached Files
    Regards,
    Bill Mosca, Microsoft Access MVP 2006-2016
    That'll do IT http://thatlldoit.com

Posting Permissions

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