How to combine mutiple column with comma separator?

Question

I have 11 columns as Note1,Note2,Note3,......Note11. I have write a query 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?

Answer 1

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;