Concantenate all records from one field

[mikehoot]

I am having a problem and getting a headache!!!

I would like to concantenate all of the records from one field which have related records in two other fields. e.g.

tblCars fldCarID fldCarName fldFuel fldWheels
1 BMW Petrol 4
2 Ford Petrol 4
3 Ferrari Petrol 4

I would like to return all records which have (for example) fldFuel = Petrol And
fldWheels = 4

and cocantenate these into a control to read "BMW, Ford, Ferrari"

I am familiar with basic SQL and could create the neccessary to return the required records but I am stuck with trying to concantenate.

Any help will be much appreciated B-)

 

[LonnieJohnson]

Here is an iteration process that you can put in an event (The click of a button, after update of something...)

Dim db as Database
Dim rs as Recordset
Dim strName as String
Dim intCnt as Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblCars&quot
intCnt = 0


Do Until rs.EOF
intCnt = intCnt + 1

If rs!fldFuel = "Petrol" And rs!fldWheels = 4 Then

If intCnt = 1 Then 'See if this is first rec
strName = rs!fldCarName
Else 'If not first rec add a comma
strName = strName & "' " & rs!fldFuel
End If

End If

rs.MoveNext

Loop

MyTextBox = strName

ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

 

[mikehoot]

Thanks for that...

I got the code to work after a little bit of tweaking! You may have guessed that I didn't really want to work with cars, but it got me there.

Thanks B-)