Concatenating (1 Viewer)

[Gismo]

Hi all,

I am joining 2 fields with a special character in between

when no data, I dont want to see the special character

I use the below code but gives an error

any suggestions, sure I am missing something small?

BatchExpire: [Transactions - Procurement - Temp]![Batch Number] & (" - "+[Transactions - Procurement - Temp]![Expiry Date])

When there is no data, the form displays correctly
When data is entered i get an #error

When I use & instead of +, The "-" is displayed when do data
With data, works fine

 

[MajP]

I just explained with example

On Event Click Error 400

Hi - getting the above error but only in the accde version (so I don't know where VBA is broken). In full accdb file, no problem. I confirmed all References are good. Suggestions please?

www.access-programmers.co.uk

I am not sure why that would fail. When you say "no data" do you mean "Expiry Date"? I assume you always have a Batch Number.
Please tell me the table name is inherited and you did not do that on your own. You would possible win the award for worst table name ever.
I would first try without the parentheses. It should not make a diff, but maybe that is forcing a calculation instead of concatenation

 

[MajP]

Worse case you could rewrite (though I would expect it would work and I would do Null Propagation like you did)

BatchExpire: [Transactions - Procurement - Temp]![Batch Number] & iif(isdate([Transactions - Procurement - Temp]![Expiry Date])," - " & [Transactions - Procurement - Temp]![Expiry Date],"")

 

[Gismo]

Worse case you could rewrite (though I would expect it would work and I would do Null Propagation like you did)

BatchExpire: [Transactions - Procurement - Temp]![Batch Number] & iif(isdate([Transactions - Procurement - Temp]![Expiry Date])," - " & [Transactions - Procurement - Temp]![Expiry Date],"")

Thank you

Much appriciated