Solved How to concatenate two fields in report - Filtering for text with space (1st record full text, 2nd record only text before the space) (1 Viewer)

[Alt]

1st fieldname is Machine_No
2nd filedname is ID_Color

If Machine_No: A-12345 & ID_Color: red/blue colors
I want to have: A-12345-red/blue

Is it possible?
Thank for your help!

 

[CJ_London]

Machine_no & “-“ & id_color

 

[Alt]

My problem is (when doing a report):
1st record:
If Machine_No content = "A-12345" & ID_Color content = "red/blue colors"
I want to see: "A-12345-red/blue"
2nd record:
If Machine_No content = "A-12345" & ID_Color content = "blue color"
I want to see: "A-12345-blue" but not "A-12345-blue color"

 

[Pat Hartman]

Your example is not clear. It seems that the first value is the contents of Machine_No. and the second value is the contents of ID_Color but we have no idea what is wrong with the results. You keep saying what you want but not what is wrong with the results. CJ's suggestion is the correct syntax.

If the problem is that you are using table level lookups, that will interfere with your results because what you see in the table isn't what you will see once you concatenate the two values.

ARE YOU using table level lookups? ARE YOU seeing the ID's when you want to see the text?

 

[Alt]

Your example is not clear. It seems that the first value is the contents of Machine_No. and the second value is the contents of ID_Color but we have no idea what is wrong with the results. You keep saying what you want but not what is wrong with the results. CJ's suggestion is the correct syntax.

If the problem is that you are using table level lookups, that will interfere with your results because what you see in the table isn't what you will see once you concatenate the two values.

ARE YOU using table level lookups? ARE YOU seeing the ID's when you want to see the text?

Hi Pat, I've put a little bit more infos on my request. Take a look when you can.
Thanks

 

[Alt]

Machine_no & “-“ & id_color

Thansk but I need to filter on id_color content (text before the " " spacing)

 

[Pat Hartman]

You still haven't explained what is wrong with CJ's suggestion.

Please post actual sample data of what you are seeing and a second sample of what you want to see. You might want to post your schema and query as well.

 

[Alt]

You still haven't explained what is wrong with CJ's suggestion.

Please post actual sample data of what you are seeing and a second sample of what you want to see. You might want to post your schema and query as well.

Thread title have been modified to show what I really need. Thanks

 

[Pat Hartman]

I have NO IDEA what is in your tables or how to achieve the requested result. Unless you can provide the necessary information, I can't help. Given what you have told us, my response is to just fix the value of ID_Color since it isn't what you want. I have no idea how that field gets populated. It appears to be a deep, dark secret. But if the data is coming from a table or value list, FIX the source.

 

[Alt]

I have NO IDEA what is in your tables or how to achieve the requested result. Unless you can provide the necessary information, I can't help. Given what you have told us, my response is to just fix the value of ID_Color since it isn't what you want. I have no idea how that field gets populated. It appears to be a deep, dark secret. But if the data is coming from a table or value list, FIX the source.

Values are coming from query and/or tables. I can't fix values into ID_Color (must filter for text before the space).

 

[Gasman]

Use Instr() to find the space and Left() to get the text before it.

 

[Alt]

Use Instr() to find the space and Left() to get the text before it.

Exactly what I'm trying right now.
Thanks