Solved Concatenate records from a query in 1 line

[Hulk009]

Hi

any help to merge all records in 1 field at the form footer with number list

Check Form called "OFF"



Will these way work for me:



=ConcatRelated("iddate", "dailyoff", "iddate =" & [dt]



I just want a simple way to merge the records in the form called "off" in 1 line with number list before every name seperated by "-"

 

[GPGeorge]

Search here for discussions of "SimpleCSV".

 

[theDBguy]

Not sure if this is what you mean.

 

[June7]

There is no function ConcatRelated() in your db so No, it won't work.

Here is Allen Browne's ConcatRelated() function http://allenbrowne.com/func-concat.html

Do you want output like: 1-Alex, 2-Tom, 3-Perla, 4-George, 5-Peter

A little modification of Allen's code can do that. First add variable:
Dim x As Integer

Then modify loop to use:

    'Loop through the matching records
    Do While Not rs.EOF
        If bIsMultiValue Then
            'For multi-valued field, loop through the values
            Set rsMV = rs(0).Value
            Do While Not rsMV.EOF
                If Not IsNull(rsMV(0)) Then
                    x = x + 1
                    strOut = strOut & x & "-" & rsMV(0) & strSeparator
                End If
                rsMV.MoveNext
            Loop
            Set rsMV = Nothing
        ElseIf Not IsNull(rs(0)) Then
            x = x + 1
            strOut = strOut & x & "-" & rs(0) & strSeparator
        End If
        rs.MoveNext
    Loop

Expression in textbox: =ConcatRelated("NamePrename","DailyOff","IDDate=#" & CDate([IDDate]) & "#")

Strongly advise not to use spaces nor punctuation in naming convention.

 

[Hulk009]

Yes this is . Thank you.

 

[June7]

And if you want the names alphabetical:
=ConcatRelated("NamePrename","DailyOff","IDDate=#" & CDate([IDDate]) & "#","NamePrename")

 

[pbaldy]

This appears to be a continuation of:

Solved - Concat

Hi any help to merge all records in 1 field at the form footer with number list Check Form called "OFF" Thanks for your help

www.access-programmers.co.uk

 

[Gasman]

This appears to be a continuation of:

Solved - Concat

Hi any help to merge all records in 1 field at the form footer with number list Check Form called "OFF" Thanks for your help

www.access-programmers.co.uk

Seems to be the norm for some people, just crossposting, even after having responses.

 

[pbaldy]

Seems to be the norm for some people, just crossposting, even after having responses.

In this case I'd wonder if the OP misinterpreted Pat's comments and thought they were supposed to re-post the question in a different forum.

 

[Gasman]

Well I thought she was just moving it to a more appropriate forum.
Easy to check it it is there, I would have thought.
At least it was under the same username this time.

 

[Pat Hartman]

Sorry if I caused confusion. While I was typing, I think someone answered the question.

 

[Gasman]

Sorry if I caused confusion. While I was typing, I think someone answered the question.

No confusion on my part.

 

[Cotswold]

Wouldn't #8 by cheekybuddha not be a solution to this post?

In MODULES
DLookup & Concatenate

Solved - DLookup & Concatenate

I need to concatenate "FirstName" and "Surname" from a table with a lookup on a form, I am stuck on how to use concatenate in the code. The code I have under the afterUpdate event for the textbox RefStaffN is: Me.FullName = DLookup("FirstName", "tblEmployee", "StaffN=" & Me!RefStaffN) This is...

www.access-programmers.co.uk

 

[Gasman]

I have used AB's ConcateRelated in the past, but tend to point these days to @theDBguy 's SimpleCSV which is simpler in my opinion.