1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Development An Access/SQL query question about concatenation

Discussion in 'Software' started by Shirty, 2 Sep 2016.

  1. Shirty

    Shirty W*nker! Super Moderator

    Joined:
    18 Apr 1982
    Posts:
    12,474
    Likes Received:
    1,694
    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!
     
  2. Goatee

    Goatee Well-Known Member

    Joined:
    19 Apr 2015
    Posts:
    1,831
    Likes Received:
    727
    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.
     
  3. danielmartind

    danielmartind New Member

    Joined:
    18 Jul 2010
    Posts:
    24
    Likes Received:
    2
    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
     
  4. [PUNK] crompers

    [PUNK] crompers Dremedial

    Joined:
    20 May 2008
    Posts:
    2,909
    Likes Received:
    50
    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?
     
  5. Shirty

    Shirty W*nker! Super Moderator

    Joined:
    18 Apr 1982
    Posts:
    12,474
    Likes Received:
    1,694
    Thanks for your responses dudes. What I actually did in the end (just in case this appears in the Google search results :lol:) 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 :)
     

Share This Page