• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

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?

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#];
0
zimmer9
Asked:
zimmer9
1 Solution
 
Mrugesh1Commented:
Use below condition...
IIf(IsNull(a.CTREF),' ',a.CTREF)  
0
 
zimmer9Author 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?
0
 
peter57rCommented:
You are adding a comma here...

CTREF)+','+IIf(IsNull(a.AgentCTREF
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now