Not showing some information in combined fields

[Anne Hecht]

I have a query:

Articles: ([Articles.Author1] & "" & [Articles.Author2] & " " & [Articles.ArticleName] & " " & [Articles.PubInfo] & "PMC ID" & [Articles.PubMedNumber] & ".")

But some of the items don't have a PubMedNumber and as you can see I have "PMC ID" hard coded to precede the PubMedNumber. Is there any way to not have it print if there is no PubMedNumber. I think there isn't, but you guys are a lot smarter than me.....

EggHeadCafe - Software Developer Portal of Choice
Imagelink HTML helper class for Asp.net MVC
http://www.eggheadcafe.com/tutorial...a-adfc155e3a77/imagelink-html-helper-cla.aspx

 

[John Spencer]

Sneaky way is to use the + concatenation operator instead of the &
concatenation operator. This relies on the fact that combining NULL with
anything (using +) will return null. So as long as PubMedNumber is actually
null and not a zero-length string, this should work.

Articles: [Articles.Author1] & "" & [Articles.Author2] & " " &
[Articles.ArticleName] & " " & [Articles.PubInfo] & ("PMC ID" +
[Articles.PubMedNumber]) & "."

Or use an IIF clause and test for both Null and a zero-length string

Articles: [Articles.Author1] & "" & [Articles.Author2] & " " &
[Articles.ArticleName] & " " & [Articles.PubInfo] & IIF(PubMedNumber & "" =
"","","PMC ID" & [Articles.PubMedNumber] & "."

I'm worried about your brackets. Normally, I would see [Articles].[Author1]
and not [Articles.Author1]. Are you sure you posted the actual expression you
are using? It is usually best if you copy and paste the actual expression
(code). If you don't you may introduce typos, that will lead to people trying
to fix the wrong thing.

I would expect to see the expression as
Articles: [Articles].[Author1] & "" & [Articles].[Author2] & " " &
[Articles].[ArticleName] & " " & [Articles].[PubInfo] &
IIF([Articles].[PubMedNumber] & "" = "","","PMC ID" &
[Articles].[PubMedNumber] & "."


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County