getting first, last and middle record

[Guest]

Hi All,
I have a table Schedule which has jobs for each staff member. they can have
up to 3 jobs per day. I have a crosstab query that displays the jobs for each
day. in the crosstab there is a IF statement for the count. if there is 1
job, it displays the first job. if there are 2 jobs, it shows the first and
last jobs. i am having trouble if there are 3 jobs. is there any way to show
the second or MID value of an instance. my IF statement is below. for count =
3 at the moment i have LAST in twice. i need to replace one of these with the
second or middle instance of the count.
I would appreciate any help with this.

IIf((Count([Client])=2),First([CLIENT]) & " " &
Last([CLIENT]),IIf((Count([Client])=1),First([CLIENT]),IIf((Count([Client])=3),First([CLIENT]) & Last([CLIENT]) & Last([CLIENT]),"")))

Thanks

Neil

 

[Guest]

Your object isn't really clear. You are telling us what you are attempting to
do but we don't know your starting data and your expected/desired result.

There are some sample crosstab reports at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane as well as
a generic concatenate function that can combine multiple records that can be
used in a crosstab.