We help IT Professionals succeed at work.

Coalesce equivalent in MS Access

Is there an equivalent for this sql statement in ms access?

SELECT LTRIM(RTRIM(COALESCE(T.Name1, N'') + N' ' + COALESCE(T.Name2, N'')))
FROM   dbo.tbl1 T;
Comment
Watch Question

Senior Oracle DBA
Commented:
Access has the NZ function, which is similar to COALESCE.

Access also has a TRIM function, which combines LTRIM and RTRIM.

Access uses & for string concatenation.

So, this should be equivalent:

TRIM(NZ(T.Name1, '') & ' ' & NZ(T.Name2, ''))

Pretty easy to google those 3 things.
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])
johnsoneSenior Oracle DBA

Commented:
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.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

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