Format a text box!

[Bob Vance]

[tblInvoice].[ClientDetail] Only appears in the table when there is no
funGetHorse, what i trying to do is get foward slash "/" to appear in front
of tblInvoice.ClientDetail and not show after FunGetHorse and OwnerPercent
As you see below "/" is appearing after OwmerPercent
Pancho @ 100%/ (this is wrong)
Joe Blogs / ClientDetail (This is Correct)




SELECT tblInvoice.OwnerID, tblInvoice.OwnerName, tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " @ " & Format(tblInvoice.OwnerPercent,"0.0%"))&("/"&
[tblInvoice].[ClientDetail]) AS Description, tblInvoice.OwnerPercentAmount
AS AmountSummary, tblInvoice.InvoiceID, tblInvoice.InvoiceNo,0 AS Flag

 

[John Spencer]

IF ClientDetail is NULL then you can use this concatenation trick.
"XXX" & Null returns "XXX"
"XXX" + Null returns Null

IIF(tblInvoice.ClientInvoice=true
,tblInvoice.OwnerName
,funGetHorse(tblInvoice.InvoiceID)
& " @ " & Format(tblInvoice.OwnerPercent,"0.0%"))
& ("/" + [tblInvoice].[ClientDetail])

OR more general and not relying on the "concatenation trick" you can use the
following if ClientDetail might be a zero-length string "" or null.

IIF(tblInvoice.ClientInvoice=true
,tblInvoice.OwnerName
,funGetHorse(tblInvoice.InvoiceID)
& " @ " & Format(tblInvoice.OwnerPercent,"0.0%"))
& IIF(Len([ClientDetail & "") = 0,"",("/" & [tblInvoice].[ClientDetail]))


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