ajaeclarke
asked on
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
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
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
ASKER
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("q ryPDTOPPRI CE")
There are two parameters in the query. Would this cause this error?
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("q
There are two parameters in the query. Would this cause this error?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
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...
ASKER
All good....I have made a couple of changes and now you code is working wonderfully.
Thanks for your help.
Thanks for your help.
Great!
;-)
Jeff
;-)
Jeff
Put a button on your form to run code like this:
Dim rst As Recordset
Dim strRecords As String
Set rst = CurrentDb.OpenRecordset("Y
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