More efficient way of concatenating DAO fields

[AvGuy]

I need to concatenate fields in a table using DAO. My problem is that there can be up to 40 fields to be joined and the code is getting rather lengthy. I don't know how many fields there are for each record until the field is tested for a null value. The only approach that I can think of is pretty ugly. Assume F1 to F40 are the fields of the recordset, and stTemp is the variable used to collect the string.

If not IsNull(!F1) then stTemp = StTemp & !F1 else exit
If not IsNull(!F2) then stTemp = stTemp & !F2 else exit

You get the idea. Should be a more elegant way than this crude, brute force method.

A for-next loop won't work because the field names can't be constructed from a variable. For-each doesn't work on recordsets. Do-While is attractive, but I can't figure any way of using a variable to represent the field names.

Any ideas?

AvGuy

 

[Eupher]

AvGuy,

You *can* declare a field variable, then iterate through the Fields collection of a recordset with a For Each statement, i.e.:

Dim CurDB As DAO.Database
Dim Rs As DAO.Recordset
Dim Fld As DAO.Field
Dim stTemp As String

Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset("tblMyTable")

Rs.MoveFirst

Do While Not Rs.EOF
     stTemp = vbNullString
     For Each Fld In Rs.Fields
          If Not IsNull(Fld) Then
               stTemp = stTemp & Fld
          End If
     Next Fld
     Debug.Print stTemp  'or do something else with the string variable here
     Rs.MoveNext
Loop

Rs.Close
Set Rs = Nothing
Set CurDB = Nothing

HTH...

Ken S.

 

[cmmrfrds]

Select (fld1&fld2&fld3&fld4) as mystring
From theTable