Parse Data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, Please help...

I have a query that pulls back all visit enquires on a job. from the results
its shows me multiple rows for the same job. Is there a way of taking all
this data and placing it all onto 1 row?

I have been doing this in excel but I am now reaching the end of rows in
excel and in need of an easier way to show this data.

In my query I have a primary key via a Job Num, and another colum to show me
what visit were up to.

How can I do this?
Jez
 
Jez:

You can do this by creating a function which loops through the rows in a
recordset and concatenates the values of the column from the Enquiries table
for the current job. Put a function along these limes in a standard module:

Public Function GetEnquiries(lngJobNumAs Long) As String

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strEnquiries As String

strSQL = "SELECT Enquiry FROM Enquiries " & _
"WHERE [Job Num] = " & lngJobNum

Set rst = New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.Open _
Source:=strSQL, _
CursorType:=adOpenKeyset, _
Options:=adCmdText

Do While Not .EOF
strEnquiries = strEnquiries & "; " & _
.Fields("Enquiry")
.MoveNext
Loop
.Close

' remove leading space and semi-colon
strEnquiries = Mid$(strEnquiries, 3)
End With

Set rst = Nothing
GetEnquiries = strEnquiries

End Function

You'll probably need to change the table and column names in the code from
Enquiries and Enquiry to match your actual names of course.

Base the query solely on the Jobs table and call the function by putting
this in the 'field' row of a blank column in query design view:

EnquiryList: GetEnquiries([Job Num])

Ken Sheridan
Stafford, England
 
Ken said:
Dim rst As ADODB.Recordset
<snipped>
With rst
<snipped>
Do While Not .EOF
strEnquiries = strEnquiries & "; " & _
.Fields("Enquiry")
.MoveNext
Loop
Click to expand...

Instead of a loop, use the recordset's GetString method e.g.

strEnquiries = .GetString(adClipString, , "; ", "; ")

Jamie.

--
 
Back
Top