Im trying to concatenate a bunch of fields (50 arghh) which each are either blank or just contain one letter. This was someone elses setup for an attendance register which I think is an odd way of doing it. I would have used one field and then to get the mark for a week take a substring at the appropriate position. Anyway im trying to replace an empty string field with a letter to represent the register hasn't been marked using sql statement:

SELECT [400 Student Marks].acad_period, [400 Student Marks].student_id, [400 Student Marks].register_id, [400 Student Marks].register_group, Replace([1],"","U") AS attendance
FROM [400 Student Marks];

[1] being the first register week then I would have concatenated with [2] etc...

This however causes an error each time on the attendance field so im guessing replace doesn't work on empty strings. Is there a way round this/alternative.

Thanks for any advice.

Ok managed to fix it myself wrote a function:

Public Function ReplaceNull(ByVal attendanceSingle As Variant) As Variant
If IsNull(attendanceSingle) Then
ReplaceNull = "-"
Else
ReplaceNull = attendanceSingle
End If
End Function

called it from the sql as I would call replace