Concatenate multiple values into one string?

[dubmunkey]

Hi all,

i have a bit of a bizarre one that im sure is possible but im missing a trick-

basically i have to join the values of a table from this:

Term Week Award
Summer 3 jon for was helping raise charity money,
Summer 3 lydia for getting top marks,

into

Term Week Awardee
Summer 3 jon for was helping raise charity money, lydia for getting top marks,

Private Sub FillHTA_Click()
Dim source As DAO.Recordset
Dim dest As DAO.Recordset
Set source = DBEngine(0)(0).OpenRecordset("qxHeadAwards")
Set dest = DBEngine(0)(0).OpenRecordset("txCompiledHeadAwards")
 
Comp = source.Fields("Award")

source.MoveLast
Award = source.RecordCount
source.MoveFirst
 
source.MoveFirst
 
dest.AddNew
dest.Fields("Term") = Me.TermCombo.Value
dest.Fields("Week") = Me.WeekCombo.Value
dest.Fields("Awardees") = source.Fields("Award")(0) + source.Fields("Award")(1)

dest.Update
End Sub

but am now stuck- am thinking that i theory i should be able to count the aviable records and then use that figure as the upperbound of the index used in when compiling dest.Fields("Awardees")

is this possible? ive done this other way round- splitting a string into fields but not this way

cheers

 

[Rich]

Why are you storing values that can be derived from a query using a function?

 

[dubmunkey]

do you mean this part:

Comp = source.Fields("Award")

i should have removed that from my code, it was left over from my first approach