I am having a problem and getting a

[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-)

 

[lameid]

You could use a recordset to get the value you want but a list box may work just as well (certainly easier). I'm still using ACC97 so forgive the DAO.

Dim RS as recordset
Dim strTmp as string

set rs = currentdb.openrecordset ("select fldCarName from tblcars where fldFuel = ""Petrol"" And fldWheels = 4&quot

If not rs.eof then
strTmp = RS!fldCarName
RS.movenext
End if

While not Rs.eof

strTmp = strTmp & ", " & RS!fldCarName

Rs.movenext

Wend

'Set a control to strTmp
Forms!FormName!ControlName = strTmp