concatenation for a field, keeping the masking data

[finsmith]

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,

[June7]

If you want to save the constant characters, set the mask as:

!000\V/00"Ph"/00"Hz";0;_

Don't use + as concatenation operator. Always use & for concatenation. I expect all those parentheses will cause an issue.

Why do you want to concatenate fields?

[finsmith]

putting all fields in one column for reporting.
the + is the iif statement.
it works great, the parentheses do not cause a problem.
The problem lies is when it is being carried over from either the table.field data or query.field
thank you for your quick response. I will try to see if this works.

[finsmith]

FYI Ive put the above in to a query in the input mask field.
It works, but again how to get that value carried over into my concat field. is it possible to make a field in a query, and then again query that same field over or a subqyery?

[Missinglinq]

...putting all fields in one column for reporting...

If you actually want to do this for a Report, you'd be better of doing the concatenation in the Report, rather than in the Query.

Many things done in Queries do not carry through to Reports simply because the capability is already available to Reports.

Linq ;0)>

[June7]

If you set the InputMask property to save input with the constant characters then that is the data in the table and will carry through. However, changing the InputMask property now will not change data already in the table. Need to fix the existing data. Even better might be to put those three values in separate fields.

[finsmith]

ive tried other things like mytbl: [electrical_data] (with the field masked) ( masked in the query and not the table)
and then called in the field again newmytbl: [mytbl] && [as_full_description] and it still gives the output as 4806003

[June7]

Must set the InputMask property of textbox on form in order to save the input data with the constant characters. Anywhere else is just formatting and does not alter data in table (unless is used in UPDATE or INSERT sql action or in code to populate a field of recordset) and does not carry through SELECT queries when the field is used in an expression. Otherwise, set format in the final query that is the RecordSource for report or put the concatenation expression in textbox.

Even if the parens aren't causing issues, they aren't needed. Still better to use & for concatenation operator instead of +. And you are doing more concatenation than necessary.

[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

I still think would be better to have data in separate fields: Volts, Phase, Hertz.

[finsmith]

I've been using the + as the If statement. There may not be data in the field. I want it to skip over that field.

[June7]

Never seen this technique. Seems to me you could end up with extraneous comma in the string, such as if the the last item is null.

[Missinglinq]

...Never seen this technique. Seems to me you could end up with extraneous comma in the string, such as if the the last item is null...

This is a much used technique for concatenation where Spaces and/or Delimiting Characters are used and where there may be Nulls. The whole purpose of the technique, if used properly, is to not include inappropriate spaces or commas or periods. With this example

CompleteName = [FirstName] & " " & ([MidInitial] + ". ") & [LastName]

Values of Xaviar, F and Cugat

will generate

Xaviar F. Cugat

while Values of Xaviar and Cugat (with no middle initial)

will generate

Xaviar Cugat (no period or unneeded, extra space)

By using the plus sign to concatenate the MidInitial, the following period and space, and because adding anything to a Null value generates a Null, the bit of code

& ([MidInitial] + ". ") &

will only concatenate the period and the following space if MidInitial is not Null. If it is Null, period and the following space will be omitted.

Linq ;0)>

[June7]

Never seen but I understand and like, however, from what I can see of the OP's expression could end up with:

value1, value2, value3, value4, value5, value6, value7,.