Conditional Concatenation in Reports

[Keldar]

Hi,

I have an annoying little problem in that I am trying to report on a list od
Departments, Dept Heads and Deputies so I have the following in my report:

=DepartmentName & IIf(IsEmpty([DeptHeadName]),""," (" & [DeptHeadName] &
IIf(Isempty([DeptDeputyName]),")"," / " & [DetDeputyName] & ")"))

So what I exect to get is say

Marketting (Barney Rubble)
or
Purchasing (Fred Flintstone / Britney Spears)

what I am getting is

Marketting (Barney Rubble / )

oe in a department where no head and deputy have been assigned yet I get

Adminiatration ( / )
rather than just
Administration

I have tried IsNull instead of IsEmpty
I have tried DeptHeadName = ""
and
DeptHeadName = " "

It all seems so simple in the help and on the examples I have found on the
web... where am I going wrong...?

Any help appreciated as always

Ian

[Bas Cost Budde]

How about

departmentname & iif(isnull(deptheadname) and
isnull(deptdeputyname),"","(") & deptheadname +
iif(isnull(deptdeputyname),"","/") & deptdeputyname &
iif(isnull(deptheadname) and isnull(deptdeputyname),"",")")

the + trick will show both ends if they are not null (and the / is never
null of course) and if there would always be a deptheadname, the whole
expression would be a lot simpler. But I read it isn't that simple.

It takes some evaluation. Maybe you can put some of the calculations in
the datasource?

Keldar wrote:

Hi,

I have an annoying little problem in that I am trying to report on a list od
Departments, Dept Heads and Deputies so I have the following in my report:

=DepartmentName & IIf(IsEmpty([DeptHeadName]),""," (" & [DeptHeadName] &
IIf(Isempty([DeptDeputyName]),")"," / " & [DetDeputyName] & ")"))

So what I exect to get is say

Marketting (Barney Rubble)
or
Purchasing (Fred Flintstone / Britney Spears)

what I am getting is

Marketting (Barney Rubble / )

oe in a department where no head and deputy have been assigned yet I get

Adminiatration ( / )
rather than just
Administration

I have tried IsNull instead of IsEmpty
I have tried DeptHeadName = ""
and
DeptHeadName = " "

It all seems so simple in the help and on the examples I have found on the
web... where am I going wrong...?

Any help appreciated as always

Ian

--
Bas Cost Budde

[Danny J. Lesandrini]

There's a really cool solution to this. Use the ampersand (&) mixed with
the plus sign (+) to filter nulls.

Result = [DeptHeadName] & (" / " + [DetDeputyName])

If DetDeputyName is null, plussing it with a string text results in NULL.
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"Keldar" <ia*******@keldar.freeserve.co.uk> wrote ...

Hi,

I have an annoying little problem in that I am trying to report on a list od
Departments, Dept Heads and Deputies so I have the following in my report:

=DepartmentName & IIf(IsEmpty([DeptHeadName]),""," (" & [DeptHeadName] &
IIf(Isempty([DeptDeputyName]),")"," / " & [DetDeputyName] & ")"))

So what I exect to get is say

Marketting (Barney Rubble)
or
Purchasing (Fred Flintstone / Britney Spears)

what I am getting is

Marketting (Barney Rubble / )

oe in a department where no head and deputy have been assigned yet I get

Adminiatration ( / )
rather than just
Administration

I have tried IsNull instead of IsEmpty
I have tried DeptHeadName = ""
and
DeptHeadName = " "

It all seems so simple in the help and on the examples I have found on the
web... where am I going wrong...?

[Keldar]

I think I get your Jist...

I am back in the office tomorrow and will try it out

Thanks

"Bas Cost Budde" <ba*@heuveltop.org> wrote in message
news:bv**********@news2.solcon.nl...

How about

departmentname & iif(isnull(deptheadname) and
isnull(deptdeputyname),"","(") & deptheadname +
iif(isnull(deptdeputyname),"","/") & deptdeputyname &
iif(isnull(deptheadname) and isnull(deptdeputyname),"",")")

the + trick will show both ends if they are not null (and the / is never
null of course) and if there would always be a deptheadname, the whole
expression would be a lot simpler. But I read it isn't that simple.

It takes some evaluation. Maybe you can put some of the calculations in
the datasource?

Keldar wrote:

Hi,

I have an annoying little problem in that I am trying to report on a list od Departments, Dept Heads and Deputies so I have the following in my report:
=DepartmentName & IIf(IsEmpty([DeptHeadName]),""," (" & [DeptHeadName] &
IIf(Isempty([DeptDeputyName]),")"," / " & [DetDeputyName] & ")"))

So what I exect to get is say

Marketting (Barney Rubble)
or
Purchasing (Fred Flintstone / Britney Spears)

what I am getting is

Marketting (Barney Rubble / )

oe in a department where no head and deputy have been assigned yet I get

Adminiatration ( / )
rather than just
Administration

I have tried IsNull instead of IsEmpty
I have tried DeptHeadName = ""
and
DeptHeadName = " "

It all seems so simple in the help and on the examples I have found on the web... where am I going wrong...?

Any help appreciated as always

Ian

--
Bas Cost Budde

[Bas Cost Budde]

Welcome.
Not a native english speaker, I'd like to understand the 'feel' conveyed
by Jist. Would you like to tell me the short story?

Keldar wrote:

I think I get your Jist...

--
Bas Cost Budde

[Bas Cost Budde]

My first thought too; it seems however that deptheadname can be null as
well, in which case he's left with that '/'

Danny J. Lesandrini wrote:

There's a really cool solution to this. Use the ampersand (&) mixed with
the plus sign (+) to filter nulls.

Result = [DeptHeadName] & (" / " + [DetDeputyName])

If DetDeputyName is null, plussing it with a string text results in NULL.

--
Bas Cost Budde

[rkc]

"Bas Cost Budde" <ba*@heuveltop.org> wrote in message
news:bv**********@news2.solcon.nl...

Welcome.
Not a native english speaker, I'd like to understand the 'feel' conveyed
by Jist. Would you like to tell me the short story?

Keldar wrote:

I think I get your Jist...

The correct spelling is gist.

http://dictionary.reference.com/search?q=gist

[Bas Cost Budde]

Thanks.
Amazing. That reference would likely eat up all my spare time on the Net...

http://dictionary.reference.com/search?q=gist

--
Bas Cost Budde