1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Concatenation and Masking Data

Discussion in 'Business Applications' started by finneyz, Jan 25, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. finneyz

    finneyz Thread Starter

    Joined:
    Jan 17, 2013
    Messages:
    39
    I have a nice laid out query, trying to keep the masking fields.
    aka !000\V/00"Ph"/00"Hz";;_
    sample, 480V/60Ph/03Hz

    when I use my query to concatenate the fields, it comes out as 4806003

    how is it possible to keep the fields with the masked inputted data? with the !000\V/00"Ph"/00"Hz";;_

    sql
    [cat_tbl.cat_name] & (","+[subcat_tbl.sub_cat_name]+",") & (" "+[copy of parts_tbl.parts_material]+",") & (" "+[copy of parts_tbl.parts_specification]+",") & (" "+[manuft_tbl.manufact_name]+",") & (" "+[copy of parts_tbl.manufact_model_no]+",") & (" "+[copy of parts_tbl.parts_finish]+ ",") & (" "+[electrical_data_tbl.electrical_data]) &"." AS full_description,

    it fits into electrical data field and table,

    I've tried it into a report and no go
    works fine in a query with the input mask .. !000\V/00"Ph"/00"Hz";0;_. but only for that expression

    Anyone know of any ideas on how I can make it print out like the above in a report, with a concatenation?
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    why do you have the Plus symbols? you just need... & you can also put single quotesbeside the comma to the inside of the fields

    (",'"[subcat_tbl.sub_cat_name]"',")


    It's easier if you post the DB so we can work with the SQL.
     
  3. finneyz

    finneyz Thread Starter

    Joined:
    Jan 17, 2013
    Messages:
    39
    Hello Ziggy. The + stands for if that there is data in the field, include the data. Otherwise you will get additional commas in the output.
    is the sql. I can post the Db Buts its very messy. still working on a lot of it. if you need it let me know


    SELECT [cat_tbl.cat_name] & (","+[subcat_tbl.sub_cat_name]) AS full_description, ELECTRICAL_DATA_TBL.ELECTRICAL_DATA AS ppp, [ppp] & (","+[full_description]) AS [money]
    FROM ELECTRICAL_DATA_TBL INNER JOIN ((UNITSOFMEASURE_TBL INNER JOIN (MANUFT_TBL INNER JOIN (SUBCAT_TBL INNER JOIN [Copy Of PARTS_TBL] ON SUBCAT_TBL.CAT_ID = [Copy Of PARTS_TBL].SUBCAT_ID) ON MANUFT_TBL.MANUFACT_ID = [Copy Of PARTS_TBL].MANUFACT_ID) ON UNITSOFMEASURE_TBL.UNITSOFMEASURE_ID = [Copy Of PARTS_TBL].UOM_DESCRIPTION) INNER JOIN CAT_TBL ON SUBCAT_TBL.CAT_ID = CAT_TBL.CAT_ID) ON ELECTRICAL_DATA_TBL.ELECTRICAL_ID = [Copy Of PARTS_TBL].ELECTRICAL_DATA;
     
  4. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    ok not sure about the + symbols, I've never used it for that purpose. I believe you are using it for the same purpose as the & does which I would use instead. The Plus might cause it to try and calculate.... try replacinge the + I was not paying attention to what the + was being used for, so what I mean would be... (",'" & [subcat_tbl.sub_cat_name] & "',")

    It is better to break your SQL down to a smaller expression (that still produces the problem) and work with that as it gets too distracting working with larger segments (IMO).
     
  5. finneyz

    finneyz Thread Starter

    Joined:
    Jan 17, 2013
    Messages:
    39
    hello zig.

    The + is to include data in the field. Otherwise extra commas will appear. Try it.
    Ive tried it in reports too. and it still does not work. even with the masking data .. It works in the query. But attempting to put it all in one field does not work and play well.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1086825

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice