Concantenation problem

[JohnSteven]

After doing a search of this forum, I solved most of my problem with the following expression;

-----------------------------------------------------------
Expr1: Nz([Corp_Code]+"/&quot & Nz([Recip_Code]+"-&quot & [Agreement_No]
-----------------------------------------------------------

"Nz" solved the problem of eliminating the "/" and "-" when Corp_Code and Recip_Code had empty fields, but now I need to eliminate the "/" after Corp_Code and/or the "-" after Recip_Code when these fields contain data and the fields that follow do not.(hope I'm making myself clear - IYIYIYI)......... ;-)

Any help would be appreciated.........

Please Post back, so other can benefit too.

Later
JohnSteven

"The only stupid question is the one you don't ask"

 

[dhookom]

It would help if you would type in a few records and what you would like returned from your expression. I doubt Nz()is doing anything in your expression.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JohnSteven]

The Expression I'm using Expr1: Nz([Corp_Code]+"/&quot & Nz([Recip_Code]+"-&quot & [Agreement_No] works fine in the first five lines of the following example, but the problem I described, comes to life on lines 6, 7, and 8 of the following example. I would like to eliminate the "/" shown on line 6 and the "-" as shown on lines 7 & 8.

  CorpCode  RecipCode  AgreementNo     Expression
----------------------------------------------------
1)---ABC-------DEF---------12345------ABC/DEF-12345
2)-------------DEF---------12345------DEF-12345
3)-------------------------12345------12345
4)---ABC-------------------12345------ABC/12345
5)-------------DEF---------12345------DEF-12345
6)---ABC------------------------------ABC/
7)-------------DEF--------------------DEF-
8)---ABC-------DEF--------------------ABC/DEF-

I hope this makes what I'm trying to accomplish clearer.

Please Post back, so other can benefit too.

Later
JohnSteven

"The only stupid question is the one you don't ask"

 

[dhookom]

There is probably a simpler expression but this should work:
Expr1: [CorpCode]+IIf(Len(Nz([RecipCode] & [AgreementNo],"&quot)=0,"","/&quot & [RecipCode]+IIf(IsNull([AgreementNo]),"","-&quot & [AgreementNo]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JohnSteven]

Thanks Duane, worked like a charm. I copied and pasted the expression into my Query and it returned the values exactly as needed............

Thanks again........

Please Post back, so other can benefit too.

Later
JohnSteven

"The only stupid question is the one you don't ask"