Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How can I get multiple records displayed in one line

Posted on 2014-12-08
8
Medium Priority
?
138 Views
Last Modified: 2014-12-09
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
0
Comment
Question by:ajaeclarke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40488171
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40488176
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
 
LVL 1

Author Comment

by:ajaeclarke
ID: 40488191
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 40488192
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40488221
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
 
LVL 1

Author Comment

by:ajaeclarke
ID: 40488236
All good....I have made a couple of changes and now you code is working wonderfully.

Thanks for your help.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40488238
Great!
;-)

Jeff
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question