asked on Coalesce equivalent in MS Access
Is there an equivalent for this sql statement in ms access?
SELECT LTRIM(RTRIM(COALESCE(T.Nam
FROM dbo.tbl1 T;
SELECT LTRIM(RTRIM(COALESCE(T.Nam
FROM dbo.tbl1 T;
Microsoft AccessSQL
Last Comment
Dale Fye
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
be careful tho, as Nz does not take an arbitrary number of parameters, it only takes 2 of them, so it is not the same thing, Also some odbc divers do not like Nz, in such case you can use IIf([T.Name1] Is Null,'',[T.Name1])
That is why I said similar. In the case it is being use, there are only the 2 parameters.
Yes you did, and that is why I said "in such case" I am merely advising to be careful should he have a need for that in the future and also giving an option in case his ODBC does not like it.
Or you can write a function:
Public Function fnFirstNonNull(Paramarray SomeValues() as Variant) as Variant
Dim intLoop as integer
fnFirstNonNull = NULL
for intLoop = lbound(SomeValues) to ubound(SomeValues)
if not IsNull(Somevalues(intLoop)) then
fnFirstNonNull = SomeValues(intLoop)
exit Function
end if
next
end function
? fnFirstNonNull(NULL, NULL, 3) => 3
? fnFirstNonNull("A", "B", "C") => A