Concatenation and Masking Data

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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?
 
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.
 

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;
 
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).
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Top