Development An Access/SQL query question about concatenation

I'm learning Access, VBA and SQL in a very simplistic way at the minute, but I've hit a stumbling block and I'm too stubborn to let it go.

I have three fields I want to concatenate in the code below: ProductName, WeightNET and Symbol. This is working exactly as you'd expect, so I'm getting output like "Jar of Raspberry Jam 300g".

My issue is that in my source data I have quite few items with a zero in the WeightNET field. In this instance, I would like the query to simply return ProductName and ignore the contents of the other two fields.

Is this possible? Everything's possible, I just don't know how to do it

Code:

SELECT [tbl_Products]![ProductName] & " " & [tbl_Products]![WeightNET] & [tbl_Products]![Symbol]
FROM tbl_Products
WHERE (((tbl_Products.Code)<>"IsNull"))
ORDER BY tbl_Products.Code;

Any guidance would be most appreciated from you SQL/Access wizards out there!

 

You could just use an iif statement over the Weightnet column to set the space, weight and identifier as blank where the Weightnet is 0.

 

How about using an iif like

SELECT [tbl_Products]![ProductName] & iif([tbl_Products]![WeightNET]>0, " " & [tbl_Products]![WeightNET] & [tbl_Products]![Symbol],"") AS Expr1
FROM tbl_Products
WHERE (((tbl_Products.Code)<>"IsNull"))
ORDER BY tbl_Products.Code;


Beat me to it Goatee

 

I would do it with a case statement (which is essentially an iif)

SELECT

CASE WHEN [tbl_Products]![WeightNET] = 0 THEN [tbl_Products]![ProductName]
ELSE [tbl_Products]![ProductName] & " " & [tbl_Products]![WeightNET] & [tbl_Products]![Symbol] END

FROM tbl_Products
WHERE (((tbl_Products.Code)<>"IsNull"))
ORDER BY tbl_Products.Code;

Makes sense?

 

Thanks for your responses dudes. What I actually did in the end (just in case this appears in the Google search results ) was as follows:

• Create one query using the criteria WeightNET >0 where the concatenation occurs.
• Create another where WeightNET <>0 with no concatenation
• UNION ALL

Bosh - that worked, but it did leave me with three queries instead of one - so I'm going to try your much more learned suggestions and go with one of those