Concantenate Records - Using WHERE and AND (CRAIG0201)

[mikehoot]

An earlier reply from Craig0201 helped with my problem but I am having a problem with the correct syntax for the SQL query.

Basically 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"

Any help will be much appreciated B-)

 

[Maquis]

How about using a listbox to list the car names instead of a textbox. That way you could set the row source of the box to be your list of cars. Maq B-)
<insert witty signature here>

 

[mikehoot]

This would not work for me I'm afraid, as I need to concantenate the records in one text box. Nice try!

 

[TTThio]

Try this one Mike.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim x As String

Set db = CurrentDb
Set rs = db.OpenRecordset(&quot;SELECT fldCarName FROM tblCars WHERE ((fldFuel = Petrol)And (fldWheels = 4))&quot

rs.MoveFirst

While Not rs.EOF
x = x & rs(&quot;fldCarName&quot & &quot;,&quot;
rs.MoveNext
Wend

x = &quot;&quot;&quot;&quot; & Left(x, Len(x) - 1) & &quot;&quot;&quot;&quot;

txt1 = x

Tin Tin