How can I get multiple records displayed in one line

Hi

I have a query that returns the below data:

PDCode      PDData                      PortZoneID
W              AP 280   Qu              3
W              AS 264   Qu              3
W              AUH2 282   Qu      3
W              AUH2 282   SG      3
W              BA 278   Qu              3
W              BU1 288   SG              3
W              CO 288   SG              3
W              F1 254   SG              3
W              F2 234   SG              3
W              GA 278   Qu              3

What I would like it to display is:

W AP 280 Qu~AS 264 Qu~AUH2 282   Qu SG~ BA 278   Qu~BU1 288   SG~CO 288   SG~F1 254   SG~F2 234   SG~GA 278   Qu

Is this possible in Access?

Thanks

Ajae
LVL 2
ajaeclarkeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Private Sub Command0_Click()

Put a button on your form to run code like this:

Dim rst As Recordset
Dim strRecords As String
Set rst = CurrentDb.OpenRecordset("YourQuery")
    rst.MoveFirst
    Do Until rst.EOF
       strRecords = strRecords & rst!PDCode & "~" & rst!PDData
        rst.MoveNext
    Loop
   
   Me.txtRecordsString = strRecords

rst.Close
Set rst = Nothing

End Sub

Put a textbox on your form named: txtRecordsString

JeffCoachman
0
Jeffrey CoachmanMIS LiasonCommented:
Private Sub Command0_Click()

Dim rst As Recordset
Dim strRecords As String
Set rst = CurrentDb.OpenRecordset("YourQuery")
    rst.MoveFirst
    Do Until rst.EOF
       strRecords = strRecords & rst!PDCode & "~" & rst!PDData
        rst.MoveNext
    Loop
   
   Me.txtRecordsString = strRecords

rst.Close
Set rst = Nothing

End Sub

Open in new window

0
ajaeclarkeAuthor Commented:
Thanks JeffCoachman.

I run the code and I get

Run-time error '3061':

Too few parameters.  Expected 2.

and Debug highlights the below:

Set rst = CurrentDb.OpenRecordset("qryPDTOPPRICE")

There are two parameters in the query.  Would this cause this error?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey CoachmanMIS LiasonCommented:
I think this code will work better:

Dim rst As Recordset
Dim strFirstLetter As String
Dim strRecords As String
Set rst = CurrentDb.OpenRecordset("YourTable")
    rst.MoveFirst
    strFirstLetter = rst!PDCode
    Do Until rst.EOF
       strRecords = strRecords & "~" & rst!PDData
        rst.MoveNext
    Loop
   strRecords = Right(strRecords, Len(strRecords) - 1)
   strRecords = strFirstLetter & " " & strRecords
   Me.txtRecordsString = strRecords

rst.Close
Set rst = Nothing

Open in new window



sample of output in a form and a report is attached
db5.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Yes, ...you never mentioned parameters in your original question.

This will not work with a parameter if it is not supplied.

But here again, you are not saying what field the parameter is in?, ...how you are specifying the parameter...
is it a simple prompt, ..or is the parameter referencing a form value...
0
ajaeclarkeAuthor Commented:
All good....I have made a couple of changes and now you code is working wonderfully.

Thanks for your help.
0
Jeffrey CoachmanMIS LiasonCommented:
Great!
;-)

Jeff
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.