I'm often facing similar problems. Trim can't handle NULL, concatenating NULL with an empty string does not result in an implicite type cast, etc...
For that I wrote an user defined routine which can all the things I need so often.
It returnes '' when given NULL and performs a TRIM and removes CRLFs.
So I only need one call for checking values.
In addition to this I use this function to unify values for loading lookup tables.
Code: Select all
If IsNull(Value) = @True
Then
lString = ""
End
Else
lString = UpCase(Value)
End
lString = Ereplace(lString," ","",0,1)
lString = Ereplace(lString,Char(10),"",0,1)
lString = Ereplace(lString,Char(13),"",0,1)
Ans = lString