How to combine mutiple column with comma separator?

I have 11 columns as Note1,Note2,Note3,......Note11. I have write a qu开发者_运维技巧ery like this to combine

SELECT DormData.BuildingID, 
        DormData.DormRoomID, 
        DormData.Item, 
        DormData.Result, 
        DormData.InspectorID, 
        DormData.Date, 
        DormData.Qty, 
        DormData.Section, 
        (Note1 & " , " 
            & Note2 & ",  " 
            & Note3 & " , " 
            & Note4 & " , " 
            & Note5 & " ,  " 
            & Note6 & " , " 
            & Note7 & ", " 
            & Note8 & ", " 
            & Note9 & ", " 
            & Note10 & ", " 
            & Note11) AS Notes, 
        DormData.Comments, 
        DormData.Resident
FROM DormData;

It works and combine my records but problem is that it is not necessary that all the notes columns have values.suppose that if in a row there is values in only Note1 and Note5 then it gives output like not1,,,,note5. but I want it show "Note1,Note5"

How can I fix this?

You could use an Iif statement in each 'Note1 * ","' section to check for null values.

Iif(IsNull(Note1), Note1, Note1 & ",")

I think that should work.

The key is IIF() aka immediate if.

For example, on the orders table in the Northwind sample database: IIF(orders.ShipRegion IS NOT NULL, orders.ShipRegion & ',', ''

Or a more complete query:

SELECT 
    orders.OrderID, orders.CustomerID, orders.EmployeeID, 

    orders.ShipVia, orders.Freight, 
    (orders.ShipName & ',' & orders.ShipCity & ',' & IIF(orders.ShipRegion IS NOT NULL, orders.ShipRegion & ',', '') & orders.ShipPostalCode & ',' & orders.ShipCountry) AS Expr1
FROM orders
WHERE orders.[OrderID]=10282;

If you want to go the vba function route, the following function will do the job:

Function JoinStrings(Delimiter As String, _
                     ParamArray StringsToJoin() As Variant) As String
Dim v As Variant
    For Each v In StringsToJoin
        If Not IsNull(v) Then
            If Len(JoinStrings) = 0 Then
                JoinStrings = v
            Else
                JoinStrings = JoinStrings & Delimiter & v
            End If
        End If
    Next v
End Function

You would call it like this:

JoinStrings(", ", Note1, Note2, Note3, Note4, Note5, Note6, Note7)

You can also use a trick with how Null expressions and concatenation works in Access:

Note1 & (", " + Note2) & (", " + Note3)...

When concatenating text, Access treats Null as it were an empty string. But if you're "adding", the Null would cause the expression inside the parentheses to result in Null. As long as your notes aren't numeric, this will work.

A modification to TheOtherTimDuncan' solution, which will work well to concatenate two (or may be three) Notes. Use IIF() to have delimiter or blank based on whether Note1 is Null. It could be like: Note1 & (IIF(Note1 Is Null, "",", ") + Note2) & (IIF((Note1 & Note2) Is Null, "",", ") + Note3)...