Join 2 controls (1 Viewer)

[Gismo]

Hi all,

I need to join 2 fields as below

Defect / Additional Work: [DAW Status Qry]![Defect / Additional Work] & " - " & [DAW Status Qry]![Cancelation Description]

If the second field is null, then I dont want the "* - *" to be displayed

please could you advise

 

[Minty]

Try this

Defect / Additional Work: [DAW Status Qry]![Defect / Additional Work] & (" - " + [DAW Status Qry]![Cancelation Description])

 

[Gismo]

Try this

Defect / Additional Work: [DAW Status Qry]![Defect / Additional Work] & (" - " + [DAW Status Qry]![Cancelation Description])

Ah, makes sense

for some reason it is not working

 

[Gasman]

Really?, it worked for me?, but I tried

tt="one word"
tt2 = null
? tt & " - " + tt2
one word

Of course that field may not be Null but ZLS instead

 

[Gismo]

Really?, it worked for me?, but I tried

tt="one word"
tt2 = null
? tt & " - " + tt2
one word

Of course that field may not be Null but ZLS instead

It works perfect when I try any other fields in this table
just not the defect and cancelation description

both fields are Text, one is long text the other is short text
The cancelation description is definitely null

 

[Gasman]

So create your own function and test with vba.

 

[Minty]

It's because it is long text I susupect.
Access doesn't play well with long text (Memo) fields.

 

[Gasman]

It's because it is long text I susupect.
Access doesn't play well with long text (Memo) fields.

@Minty
Just tested on one of my tables with a memo, and works fine on the syntax I used, which TBH was a mistake as I missed your opening ( for the "-"

SELECT Ship.ID, Ship.Ship, [unit] & "-"+[Afterbibby] AS Expr1
FROM Ship;

 

[Pat Hartman]

I really hate "try this" with no explanation so I'll explain.

The "&" is the standard concatenation character in VBA. The arithmetic operator "+" can in some cases be used to concatenate fields. The two have different properties that we can use to our advantage when one of the operands is null. The "&" treats null values as if they were ZLS whereas the "+" respects Null because that is how + works when adding a number to null, the result of any operation including a null value is null so 123 + Null = Null. If you compare a non-null value to null, the result is null, if you compare null to null, the result is null. The only time you get true or false is if neither operand is null. If abc > null = null. If abc < null = null. This can trap you when making comparisons so always take into account the potential for null when one of the operands is not defined as required at the table leve.

ABC & Null = ABC
ABC & "-" & Null = ABC- ''' this is usually not desirable and so we can use the "+" operator instead since
ABC + Null = Null
Therefore
ABC & ("-" + Null) = ABC '' I recommend using parentheses to control the order of operation to ensure the expression is evaluated as you want.

Do not take this to mean that you should always use the "+" because the primary use of the "+" is to add two numbers so

123 + 123 = 246
whereas
123 & 123 = 123123

To wrap up. Use the + whenever you want to concatenate an optional element and don't want to end up with an extraneous delimiter in a string.