IIF statement

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

I have a field called Turnaround(Days) and I want a calculated field
within a totals query to show me the minimum value from this field,
unless that happens to be zero & then I want "Same day" displayed. I
have the following expression:


Shortest Time Taken: Min(IIf([Turnaround(Days)]=0,"Same
Day",Min([Turnaround(Days)])))

(With 'Expression' in the 'Totals' row)

However it doesn't work? Could anyone advise me where I'm going wrong?

Many Thanks

Jason
 
Hi Jay,

It doesn't work as when it's zero, you are passing a string to the Min
function...
Shortest Time Taken: Min(IIf([Turnaround(Days)]=0,"Same
Day",Min([Turnaround(Days)])))

You need to get rid of the Min around the outside of the iif for starters...
Then assuming you have the fields correct etc, it should work for you.

As an aside, you should avoid using parentheses as part of a field name, as
it makes it very confusing to read...

Hope this helps.

Damian.
 
Hi Damian,

Thanks for the advice. I get what you;re saying about removing the
outside Min, but I only want to return 'Same Day' if the minimum value
in Turnaround(Days) is zero. So wouldn't removing the outside Min
remove this test?

Thanks,

Jason

Damian said:
Hi Jay,

It doesn't work as when it's zero, you are passing a string to the Min
function...
Shortest Time Taken: Min(IIf([Turnaround(Days)]=0,"Same
Day",Min([Turnaround(Days)])))

You need to get rid of the Min around the outside of the iif for starters...
Then assuming you have the fields correct etc, it should work for you.

As an aside, you should avoid using parentheses as part of a field name, as
it makes it very confusing to read...

Hope this helps.

Damian.

Jay said:
I have a field called Turnaround(Days) and I want a calculated field
within a totals query to show me the minimum value from this field,
unless that happens to be zero & then I want "Same day" displayed. I
have the following expression:


Shortest Time Taken: Min(IIf([Turnaround(Days)]=0,"Same
Day",Min([Turnaround(Days)])))

(With 'Expression' in the 'Totals' row)

However it doesn't work? Could anyone advise me where I'm going wrong?

Many Thanks

Jason
Click to expand...
Click to expand...
 
IIf(Min([Turnaround(Days)])=0,"Same Day",Min([Turnaround(Days)])

Jay said:
Hi Damian,

Thanks for the advice. I get what you;re saying about removing the
outside Min, but I only want to return 'Same Day' if the minimum value in
Turnaround(Days) is zero. So wouldn't removing the outside Min remove
this test?

Thanks,

Jason

Damian said:
Hi Jay,

It doesn't work as when it's zero, you are passing a string to the Min
function...
Shortest Time Taken: Min(IIf([Turnaround(Days)]=0,"Same
Day",Min([Turnaround(Days)])))

You need to get rid of the Min around the outside of the iif for
starters... Then assuming you have the fields correct etc, it should work
for you.

As an aside, you should avoid using parentheses as part of a field name,
as it makes it very confusing to read...

Hope this helps.

Damian.

Jay said:
I have a field called Turnaround(Days) and I want a calculated field
within a totals query to show me the minimum value from this field,
unless that happens to be zero & then I want "Same day" displayed. I
have the following expression:


Shortest Time Taken: Min(IIf([Turnaround(Days)]=0,"Same
Day",Min([Turnaround(Days)])))

(With 'Expression' in the 'Totals' row)

However it doesn't work? Could anyone advise me where I'm going wrong?

Many Thanks

Jason
Click to expand...
Click to expand...
Click to expand...
 
Cheers Damian,

I see what I was doing wrong now....many thanks,

regards,

Jay

IIf(Min([Turnaround(Days)])=0,"Same Day",Min([Turnaround(Days)])

Jay said:
Hi Damian,

Thanks for the advice. I get what you;re saying about removing the
outside Min, but I only want to return 'Same Day' if the minimum value in
Turnaround(Days) is zero. So wouldn't removing the outside Min remove
this test?

Thanks,

Jason

Damian said:
Hi Jay,

It doesn't work as when it's zero, you are passing a string to the Min
function...
Shortest Time Taken: Min(IIf([Turnaround(Days)]=0,"Same
Day",Min([Turnaround(Days)])))

You need to get rid of the Min around the outside of the iif for
starters... Then assuming you have the fields correct etc, it should work
for you.

As an aside, you should avoid using parentheses as part of a field name,
as it makes it very confusing to read...

Hope this helps.

Damian.

:

I have a field called Turnaround(Days) and I want a calculated field
within a totals query to show me the minimum value from this field,
unless that happens to be zero & then I want "Same day" displayed. I
have the following expression:


Shortest Time Taken: Min(IIf([Turnaround(Days)]=0,"Same
Day",Min([Turnaround(Days)])))

(With 'Expression' in the 'Totals' row)

However it doesn't work? Could anyone advise me where I'm going wrong?

Many Thanks

Jason
Click to expand...
Click to expand...
Click to expand...
 
Back
Top