Return text based on other field values

[klr]

I need to set up an extremely complicated expression in a query for a
subreport I have created.

Basically the query pulls through records with fields client type,
income, time taken.

I then have 7 different date fields, each representing a stage in the
job cycle:-

rcvd
rpld
apprvd
schld
strtd
com_cha
com_nocha

I want to display a control that contains the current status (Received,
Replied, Approved, Scheduled, Started, Completed (charged), Completed
(no charged) depending on which of the above date fields has an entry.
So the query needs to look at rcvd, if contains an entry it will
display Received, unless strtd contains an entry in which case it will
display Started. The difficulty I am having with deciding how best to
do this, is that all records will have an entry in rcvd, some with an
entry in apprvd, schld AND strtd etc etc.

Can anyone advise the best way I can achieve my end goal? It's
possible that I am thinking in the wrong place and a quey isn't going
to give me what I need.

Any help or advice gratefully received.

 

[Nick 'The database Guy']

Hi klr,

You need to use a nested IIf statement.

It has the following syntax:

IIf (Condition, True, IIf (Condition, True, IIf(Condition, True,
False)))

Yuo need to start with the highest value ie 'com_nocha'

Good luck

 

[John Spencer]

You could use the switch function

Switch(IsNull(Com_Nocha)=False, "Completed-No Charge",
IsNull(Com_Cha)=False, "Completed-Charged",
IsNull(Strtd)=False, "Started",
...)

 

[klr]

Brilliant! I used the Switch function as advised and it worked
perfectly for my requirements.

Thanks heaps!

KLR