Concatenate number and symbol (2 Viewers)

[Gismo]

Hi all,

Hope you are well

I tried to concatenate with + if there are no data but getting errors

Limit 1 and Unit 1 will always have data
Limit 2 and Unit 2 might not always have data

If Limit 2 has no data, I don't want to show the "/"

Please could you assist?

Limit: [Limit 1] & " " & [Unit 1] & " / " & [Limit 2] & " " & [Unit 2]

 

[arnelgp]

Limit: [Limit 1] & " " & [Unit 1] & (" / " + [Limit 2] + " " + [Unit 2])

 

[Gismo]

Limit: [Limit 1] & " " & [Unit 1] & (" / " + [Limit 2] + " " + [Unit 2])

This is what I tried
I get an #Error when there is no data in Limit 2

 

[Minty]

No, you appear to be getting an error when there IS data in limit 2.

Copy and paste exactly what is in YOUR query - not what arnelgp wrote.

 

[Gismo]

No, you appear to be getting an error when there IS data in limit 2.

Copy and paste exactly what is in YOUR query - not what arnelgp wrote.

With my code, this is what I get
I used the + before and also got the error

Limit: [Limit 1] & " " & [Unit 1] & " / " & [Limit 2] & " " & [Unit 2]

 

[Gasman]

With my code, this is what I get
I used the + before and also got the error


View attachment 109480

Not even what arnelgp supplied?

 

[Gismo]

Not even what arnelgp supplied?

No, That code gives the same error

Refer to post #3

 

[Gasman]

No, That code gives the same error

Refer to post #3

From the immediate window. on what arnelgp supplied

limit1="600"
unit1="FH"
Limit2=Null
Unit2="M"
? Limit1 & " " & Unit1 & " / " + Limit2 + " " + Unit2
600 FH

Limit2=""
? Limit1 & " " & Unit1 & " / " + Limit2 + " " + Unit2
600 FH /  M

slight amendment

limit1="600"
unit1="FH"
Limit2=Null
Unit2="M"
? Limit1 & " " & Unit1 & " / " + Limit2 & " " & Unit2
600 FH /  M

Limit2=""
? Limit1 & " " & Unit1 & " / " + Limit2 & " " & Unit2
600 FH /  M

If any are numeric, you need to convert to string with Cstr()

 

[Minty]

@Gasman It doesn't like adding the numeric value when it is null.
Look at the sample attached.
Expr3: CStr([Limit2])

 

[arnelgp]

can you change it to:

SELECT [Limit 1] & " " & [Unit 1] & iif(trim(" / " & [Limit 2] & " " & [Unit 2])="/",""," / " & [Limit 2] & " " & [Unit 2])  AS Limit, Table1.[Limit 1], Table1.[Unit 1], Table1.[Limit 2], Table1.[Unit 2]
FROM Table1;

replace Table1 with the name of your table.

Limit: [Limit 1] & " " & [Unit 1] & IIf(Trim(" / " & [Limit 2] & " " & [Unit 2])="/",""," / " & [Limit 2] & " " & [Unit 2])

 

[Gasman]

@Gasman It doesn't like adding the numeric value when it is null.
Look at the sample attached.
Expr3: CStr([Limit2])
View attachment 109481

Can't we use NZ() ?

Expr4: NZ([Limit2],"")

 

[Minty]

@Gasman - Nope because then the + doesn't work because it's no longer null leaving the / behind.

 

[Gismo]

can you change it to:

SELECT [Limit 1] & " " & [Unit 1] & iif(trim(" / " & [Limit 2] & " " & [Unit 2])="/",""," / " & [Limit 2] & " " & [Unit 2])  AS Limit, Table1.[Limit 1], Table1.[Unit 1], Table1.[Limit 2], Table1.[Unit 2]
FROM Table1;

replace Table1 with the name of your table.


View attachment 109483

Limit: [Limit 1] & " " & [Unit 1] & IIf(Trim(" / " & [Limit 2] & " " & [Unit 2])="/",""," / " & [Limit 2] & " " & [Unit 2])

Thank you so much
Looks good

 

[Gasman]

@arnelgp 's code appears to work?