How to display a concatenated field when some of the fields have a NULL or blank value using ACcess 2003 with an MDB type file?
Posted on 2011-04-29
I am developing an Access application using Access 2003 with an MDB type file.
In the following query, I concatenate values, but it seems that when 1 of the concatenated fields is empty or has a Null value (for example the field CTREF), then the entire field "refNo" has an empty value.
Do you know how to resolve this problem?
SELECT b.[GLAcct#], b.[COST CNTR], (' ') AS checkNum, (a.JournalRef+','+a.CTREF+','+a.AgentCTRef) AS refNo
FROM tblBanksConv AS b, IMNYCashBreaks AS a
WHERE left(a.BalancePool,3) & mid(a.BalancePool,5)=b.[GLAcct#];