We help IT Professionals succeed at work.

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?

zimmer9
zimmer9 asked
on
370 Views
Last Modified: 2012-05-11
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#];
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
SELECT b.[GLAcct#], b.[COST CNTR], (' ') AS checkNum, (IIf(IsNull(a.JournalRef),' ',a.JournalRef)
+','+IIf(IsNull(a.CTREF),' ',a.CTREF)+','+IIf(IsNull(a.AgentCTREF),' ',a.AgentCTREF)) AS refNo
FROM tblBanksConv AS b, IMNYCashBreaks AS a
WHERE left(a.BalancePool,3) & mid(a.BalancePool,5)=b.[GLAcct#];


Why do I always end up with a leading comma in the field refNo using the above IIf?
CERTIFIED EXPERT

Commented:
You are adding a comma here...

CTREF)+','+IIf(IsNull(a.AgentCTREF
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.