Trouble switching syntax from IIf to Switch

[Susan L]

I have a report prepared monthly that sums totals of the controls on the
form. (Underlying data type is number.)

I need to be able to calculate a percentage that is the current month's
total/20034. So the percentage changes each month as new totals are
calculated.

I started with an IIf statement, which works. But since I need 12 statements
to evaluate each month, I think I should use Switch.

Here's the IIf statement that works:
=IIf(IsNull([sumAug]),IIf(Not
IsNull([sumJul]),FormatPercent([sumJul]/20034,2)))

Here's my attempt at using Switch, which generates "Error".
=Switch(IsNull([sumAug]),Not
IsNull([sumJul]),FormatPercent([sumJul]/20034,2)),IsNull([sumSep]),Not
IsNull([sumAug]),FormatPercent([sumAug]/20034,2)))

I've searched the posts extensively, but have not been able to solve my
problem. Can anyone help?

 

[Guest]

Easier to use CHOOSE:

FormatPercent( CHOOSE( month(date),[sumJan],[sumFeb],[sumMar]
...) , /20034,2)

....and only half as long. Of course,
FormatPercent([sumThisMonth]/20034,2)
would be even better, and indicates that you should re-think
your query structure, if not your database structure.

It looks like the problem might be that your expression is just
too long and complex, and you have made a mistake, at least
here when you posted it: your SWITCH statement seems to
be missing the first value, following the first test.

(david)

 

[Susan L]

David: Thanks for responding. I'll be glad to re-think the expression
structure. (The table structure is cast in stone, unfortunately.) The shorter
the route, the better, because I have two other calculations that have to
test for a string (in one case, 7 strings) as well as the month.

However, I have some questions, as I'm pretty much an amateur.
In the CHOOSE example, it must stop at the current month identified by the
month (Date) function?

In your second approach (which of course is more appealing because it's so
short) I didn't understand the sumThisMonth. Could you explain?



--
susan

Easier to use CHOOSE:

FormatPercent( CHOOSE( month(date),[sumJan],[sumFeb],[sumMar]
...) , /20034,2)

....and only half as long. Of course,
FormatPercent([sumThisMonth]/20034,2)
would be even better, and indicates that you should re-think
your query structure, if not your database structure.

It looks like the problem might be that your expression is just
too long and complex, and you have made a mistake, at least
here when you posted it: your SWITCH statement seems to
be missing the first value, following the first test.

(david)

I have a report prepared monthly that sums totals of the controls on the
form. (Underlying data type is number.)

I need to be able to calculate a percentage that is the current month's
total/20034. So the percentage changes each month as new totals are
calculated.

I started with an IIf statement, which works. But since I need 12 statements
to evaluate each month, I think I should use Switch.

Here's the IIf statement that works:
=IIf(IsNull([sumAug]),IIf(Not
IsNull([sumJul]),FormatPercent([sumJul]/20034,2)))

Here's my attempt at using Switch, which generates "Error".
=Switch(IsNull([sumAug]),Not
IsNull([sumJul]),FormatPercent([sumJul]/20034,2)),IsNull([sumSep]),Not
IsNull([sumAug]),FormatPercent([sumAug]/20034,2)))

I've searched the posts extensively, but have not been able to solve my
problem. Can anyone help?

 

[Guest]

Yes, "Choose n, ......" chooses the n'th value from the list. Since
you seem to be choosing by month, you can just use the month
number. And I think that you can use the function form Choose( )
so that you can move the format function outside the Choose/
Switch function, but I haven't tried that: air code.

If you have a more complex choice rather than just the month
number, you can define your own function in a VBA module
with any code to choose the choice number:

CHOOSE myfunc(myDatefield),......

sumThisMonth is just my imagination of what you might get
if you re-thought your tables and queries. How did you get
the sum of March or April? You might be able to rebuild that
to get a field which is the sum of this month, rather than
building all the month sums and then choosing one.

(david)

David: Thanks for responding. I'll be glad to re-think the expression
structure. (The table structure is cast in stone, unfortunately.) The shorter
the route, the better, because I have two other calculations that have to
test for a string (in one case, 7 strings) as well as the month.

However, I have some questions, as I'm pretty much an amateur.
In the CHOOSE example, it must stop at the current month identified by the
month (Date) function?

In your second approach (which of course is more appealing because it's so
short) I didn't understand the sumThisMonth. Could you explain?



--
susan

Easier to use CHOOSE:

FormatPercent( CHOOSE( month(date),[sumJan],[sumFeb],[sumMar]
...) , /20034,2)

....and only half as long. Of course,
FormatPercent([sumThisMonth]/20034,2)
would be even better, and indicates that you should re-think
your query structure, if not your database structure.

It looks like the problem might be that your expression is just
too long and complex, and you have made a mistake, at least
here when you posted it: your SWITCH statement seems to
be missing the first value, following the first test.

(david)

I have a report prepared monthly that sums totals of the controls on the
form. (Underlying data type is number.)

I need to be able to calculate a percentage that is the current month's
total/20034. So the percentage changes each month as new totals are
calculated.

I started with an IIf statement, which works. But since I need 12 statements
to evaluate each month, I think I should use Switch.

Here's the IIf statement that works:
=IIf(IsNull([sumAug]),IIf(Not
IsNull([sumJul]),FormatPercent([sumJul]/20034,2)))

Here's my attempt at using Switch, which generates "Error".
=Switch(IsNull([sumAug]),Not
IsNull([sumJul]),FormatPercent([sumJul]/20034,2)),IsNull([sumSep]),Not
IsNull([sumAug]),FormatPercent([sumAug]/20034,2)))

I've searched the posts extensively, but have not been able to solve my
problem. Can anyone help?