Calculation Using IIf

I think it would be a lot easier if I could see a stripped down version of
your database. In looking at what you're showing me, it would seem to be
simple enough to just run an IIF that tests for the current month = the month
in the column. However, I don't know the exact structure of the report so I
can't give specific instructions.

The IIF statement would look something like:
IIF(month(now())=YourControl,YourCalculation,0)

The caveat is that the value of the control should be formatted the same as
the result of month(now())

You could do this for each month and then have one unbound textbox that sums
them or you could build a complex nesting within one control.

I hope this gets you on the right track. If not, just post back.

"Susan L" wrote:

It's a cumulative report, as in the sample, so the totals at the bottom are
there for each month.

But, yes, the percentage to be calculated is only for the current month.
--
susan


"Maverick" wrote:

Are you looking for the report to only report on the current month?

"Susan L" wrote:

Well, in my other reports with 7 rows, the stacking method was effective!!!

Now for the more complicated issue. One of the reports I'm creating has a
percentage that is calculated from the current month's total/a specific
number, e.g., In January, it's the LO1 total for txtJan/2862, in February,
LO1 total for txtFeb/2862. Same thing with CSI, etc.

The reports looks like this only with 7 rows:
Functions Jan Feb etc Total # % of Total Possible
LO1 2200 1849 etc xxxxx xx.x%
CSI 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

Is there a way to write an expression that finds the proper month's column
and performs the calculation? It's the moving to the current month that I'm
stumped on. Any thoughts?

--
susan


"Maverick" wrote:

HTH

Post back if you additional help with the more complicated report. Also, if
you feel that a post answers your question, be sure to click Yes to "Was this
post helpful to you?" as it identifies it as an answered question.

"Susan L" wrote:

Maverick: Thanks for the work around suggestion. I did the calculations in
the two text boxes and then stacked them on top of each other in the row of
controls. This works OK for this report.

For my other calculations, however, this may not be a solution, since there
are seven rows in the queries. I'll give it a try. I may have to admit failur
-- not my usual course of action -- and go back to Excel.

Thanks for your help.
--
susan


"Maverick" wrote:

Susan,

What you can do is create two textboxes. Have on run the IIF statement for
Valid and the other for Invalid. Then you can calculate based on the results.

IIF([Transactions]="Valid",1,0) for one textbox and
IIF([Transactions]="Invalid",1,0) for the other. Now you can count or sum the
total number of each and then perform your percentage calculation based off
of this.


"Susan L" wrote:

That was very hard to read. I hope this posts better.

The report I have to create looks like this:
Transactions Jan Feb etc Total # % of Total
Valid 2200 1849 etc xxxxx xx.x%
Invalid 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

--
susan


"Susan L" wrote:

Thanks for responding. I hope the following will be clearer.

The report I have to create looks like this:
Transactions Jan Feb etc Total #
% of Total
Valid 2200 1849 etc xxxxx
xx.x%
Invalid 2015 1900 etc. xxxxx
xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

The original data that comes to me is structured as:
Date Valid Invalid

I have a crosstab that places the data in rows, and the totals in columns by
month. Because the values are in a single control on the form, though, I
can’t figure out how to get a percentage calculated. The ones on the bottom
row, of course, had only one condition ("Valid"). I tried putting a column in
the xtab to calculate it but got a message that a xtab will only accept one
column.

And, this is the simplest of the calculations I’m facing. I have another
report that has 7 rows and the percentage has to be calculated by the current
month. So I was starting with the “simplest.�

Hope this clarifies. Thanks.

--
susan


"Maverick" wrote:

Susan,

Why are you trying to get two calculations in one field? You either want the
value for valid or value for invalid in one field. Your approach is trying to
say that if Transactions = Valid then give me the percentage of valid and if
Transactions Valid then if Transactions = Invalid then give me the
percentage of invalid. This is an either or calculation.

The reason you are getting the error is because you have too many arguments
for an IIF statement. The syntax is IIF(condition is true, this result, else
this result). The ,0,2 are extraneous and should be removed. I'm not sure
what you are trying to get out of the 0,2 or I would give you an alternative.

If you post a more clear description of what you need, we can possibly
provide you with more useful advice.

HTH

"Susan L" wrote:

I have a crosstab query with string values as row headings (Valid, Invalid)
and months as column headings. In a report, I need to calculate the
percentage of the total for each value. I can get a percentage for either of
the values, but not both. Something is wrong with my syntax.

Here is the expression:
=FormatPercent(IIf([Transactions]="Valid",[txtYTDTotal]/[txtGrandTotal],
IIf([Transactions]="Invalid",[txtYTDTotal]/[txtGrandTotal]),0,2)

The error message says that I have the wrong number of arguments.

Or, if there is a better way to do this, please let me know.
--
susan

Maverick: Thanks for "staying the course." I have tried out your suggestion,
but am getting a blank. If you want a copy of the database (it's really small
at this point), I'll be happy to forward.

The text boxes I would be using are those with the IIF statement you
suggested earlier. They have this expression: =FormatPercent(IIf([Function
Type]="LO1", txtJul]/2862),2)). That works. But I don't want to change the
control each month.

So, using your suggestion, I created the following:
=FormatPercent(IIf([Function Type]="LO1" And
IIf(Month(Now())=[txtJul],[txtJul]/2862),2)). This does not produce a value.
Wasn't sure exactly what you meant about the fomatting of the control.

And one more issue, I misspoke about "current" month, as this report is done
afterward. What I need is "prior" month. So do I put a -1 somewhere? Like
(Month(Now()-1)?
--
susan


"Maverick" wrote:

I think it would be a lot easier if I could see a stripped down version of
your database. In looking at what you're showing me, it would seem to be
simple enough to just run an IIF that tests for the current month = the month
in the column. However, I don't know the exact structure of the report so I
can't give specific instructions.

The IIF statement would look something like:
IIF(month(now())=YourControl,YourCalculation,0)

The caveat is that the value of the control should be formatted the same as
the result of month(now())

You could do this for each month and then have one unbound textbox that sums
them or you could build a complex nesting within one control.

I hope this gets you on the right track. If not, just post back.

"Susan L" wrote:

It's a cumulative report, as in the sample, so the totals at the bottom are
there for each month.

But, yes, the percentage to be calculated is only for the current month.
--
susan


"Maverick" wrote:

Are you looking for the report to only report on the current month?

"Susan L" wrote:

Well, in my other reports with 7 rows, the stacking method was effective!!!

Now for the more complicated issue. One of the reports I'm creating has a
percentage that is calculated from the current month's total/a specific
number, e.g., In January, it's the LO1 total for txtJan/2862, in February,
LO1 total for txtFeb/2862. Same thing with CSI, etc.

The reports looks like this only with 7 rows:
Functions Jan Feb etc Total # % of Total Possible
LO1 2200 1849 etc xxxxx xx.x%
CSI 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

Is there a way to write an expression that finds the proper month's column
and performs the calculation? It's the moving to the current month that I'm
stumped on. Any thoughts?

--
susan


"Maverick" wrote:

HTH

Post back if you additional help with the more complicated report. Also, if
you feel that a post answers your question, be sure to click Yes to "Was this
post helpful to you?" as it identifies it as an answered question.

"Susan L" wrote:

Maverick: Thanks for the work around suggestion. I did the calculations in
the two text boxes and then stacked them on top of each other in the row of
controls. This works OK for this report.

For my other calculations, however, this may not be a solution, since there
are seven rows in the queries. I'll give it a try. I may have to admit failur
-- not my usual course of action -- and go back to Excel.

Thanks for your help.
--
susan


"Maverick" wrote:

Susan,

What you can do is create two textboxes. Have on run the IIF statement for
Valid and the other for Invalid. Then you can calculate based on the results.

IIF([Transactions]="Valid",1,0) for one textbox and
IIF([Transactions]="Invalid",1,0) for the other. Now you can count or sum the
total number of each and then perform your percentage calculation based off
of this.


"Susan L" wrote:

That was very hard to read. I hope this posts better.

The report I have to create looks like this:
Transactions Jan Feb etc Total # % of Total
Valid 2200 1849 etc xxxxx xx.x%
Invalid 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

--
susan


"Susan L" wrote:

Thanks for responding. I hope the following will be clearer.

The report I have to create looks like this:
Transactions Jan Feb etc Total #
% of Total
Valid 2200 1849 etc xxxxx
xx.x%
Invalid 2015 1900 etc. xxxxx
xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

The original data that comes to me is structured as:
Date Valid Invalid

I have a crosstab that places the data in rows, and the totals in columns by
month. Because the values are in a single control on the form, though, I
can’t figure out how to get a percentage calculated. The ones on the bottom
row, of course, had only one condition ("Valid"). I tried putting a column in
the xtab to calculate it but got a message that a xtab will only accept one
column.

And, this is the simplest of the calculations I’m facing. I have another
report that has 7 rows and the percentage has to be calculated by the current
month. So I was starting with the “simplest.�

Hope this clarifies. Thanks.

--
susan


"Maverick" wrote:

Susan,

Why are you trying to get two calculations in one field? You either want the
value for valid or value for invalid in one field. Your approach is trying to
say that if Transactions = Valid then give me the percentage of valid and if
Transactions Valid then if Transactions = Invalid then give me the
percentage of invalid. This is an either or calculation.

The reason you are getting the error is because you have too many arguments
for an IIF statement. The syntax is IIF(condition is true, this result, else
this result). The ,0,2 are extraneous and should be removed. I'm not sure
what you are trying to get out of the 0,2 or I would give you an alternative.

If you post a more clear description of what you need, we can possibly
provide you with more useful advice.

HTH

"Susan L" wrote:

I have a crosstab query with string values as row headings (Valid, Invalid)
and months as column headings. In a report, I need to calculate the
percentage of the total for each value. I can get a percentage for either of
the values, but not both. Something is wrong with my syntax.

Here is the expression:
=FormatPercent(IIf([Transactions]="Valid",[txtYTDTotal]/[txtGrandTotal],
IIf([Transactions]="Invalid",[txtYTDTotal]/[txtGrandTotal]),0,2)

The error message says that I have the wrong number of arguments.

Or, if there is a better way to do this, please let me know.
--
susan

The idea is to ensure that the control that says last month was July would be
equal to Month(now())-1 which has a value of 7. So, the question is does
txtJul have a numeric value of 7? If the value of txtJul = July x, 2008...
then you should have the formula do the following:

IIF(Month(now())-1=Month(txtJul), txtJul/2862,2)

Furthermore, I think you need to change your And part of the expression. It
should be more like this:

=FormatPercent(IIf([Function Type]="LO1" And (Month(now())-1=Month(txtJul),
txtJul/2862,2)

The way you set it up it was evaluating whether ([Function Type]="LO1" and
the IIF statement about the date were both true. However, it didn't have any
value to establish if they were because the values for the IIF statement were
part of the And. I hope I explained that well. It's midnight here and I'm
getting a little fuzzy. LOL

I'm not sure why you have 2 as the value returned for a false response in
the formula. Typically you'd have it be a null value or 0. Your formula would
result in either the calculation of txtJul/2862 or 2.

In addition, is the value you divide by always going to be 2862? If not, you
should make it so that you can change this number without having to change
the code. If it changes, it would make sense to store the dividend in a table
and have it relate to the month you are calculating.

Let me know if you figure it out or need more help.

"Susan L" wrote:

Maverick: Thanks for "staying the course." I have tried out your suggestion,
but am getting a blank. If you want a copy of the database (it's really small
at this point), I'll be happy to forward.

The text boxes I would be using are those with the IIF statement you
suggested earlier. They have this expression: =FormatPercent(IIf([Function
Type]="LO1", txtJul]/2862),2)). That works. But I don't want to change the
control each month.

So, using your suggestion, I created the following:
=FormatPercent(IIf([Function Type]="LO1" And
IIf(Month(Now())=[txtJul],[txtJul]/2862),2)). This does not produce a value.
Wasn't sure exactly what you meant about the fomatting of the control.

And one more issue, I misspoke about "current" month, as this report is done
afterward. What I need is "prior" month. So do I put a -1 somewhere? Like
(Month(Now()-1)?
--
susan


"Maverick" wrote:

I think it would be a lot easier if I could see a stripped down version of
your database. In looking at what you're showing me, it would seem to be
simple enough to just run an IIF that tests for the current month = the month
in the column. However, I don't know the exact structure of the report so I
can't give specific instructions.

The IIF statement would look something like:
IIF(month(now())=YourControl,YourCalculation,0)

The caveat is that the value of the control should be formatted the same as
the result of month(now())

You could do this for each month and then have one unbound textbox that sums
them or you could build a complex nesting within one control.

I hope this gets you on the right track. If not, just post back.

"Susan L" wrote:

It's a cumulative report, as in the sample, so the totals at the bottom are
there for each month.

But, yes, the percentage to be calculated is only for the current month.
--
susan


"Maverick" wrote:

Are you looking for the report to only report on the current month?

"Susan L" wrote:

Well, in my other reports with 7 rows, the stacking method was effective!!!

Now for the more complicated issue. One of the reports I'm creating has a
percentage that is calculated from the current month's total/a specific
number, e.g., In January, it's the LO1 total for txtJan/2862, in February,
LO1 total for txtFeb/2862. Same thing with CSI, etc.

The reports looks like this only with 7 rows:
Functions Jan Feb etc Total # % of Total Possible
LO1 2200 1849 etc xxxxx xx.x%
CSI 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

Is there a way to write an expression that finds the proper month's column
and performs the calculation? It's the moving to the current month that I'm
stumped on. Any thoughts?

--
susan


"Maverick" wrote:

HTH

Post back if you additional help with the more complicated report. Also, if
you feel that a post answers your question, be sure to click Yes to "Was this
post helpful to you?" as it identifies it as an answered question.

"Susan L" wrote:

Maverick: Thanks for the work around suggestion. I did the calculations in
the two text boxes and then stacked them on top of each other in the row of
controls. This works OK for this report.

For my other calculations, however, this may not be a solution, since there
are seven rows in the queries. I'll give it a try. I may have to admit failur
-- not my usual course of action -- and go back to Excel.

Thanks for your help.
--
susan


"Maverick" wrote:

Susan,

What you can do is create two textboxes. Have on run the IIF statement for
Valid and the other for Invalid. Then you can calculate based on the results.

IIF([Transactions]="Valid",1,0) for one textbox and
IIF([Transactions]="Invalid",1,0) for the other. Now you can count or sum the
total number of each and then perform your percentage calculation based off
of this.


"Susan L" wrote:

That was very hard to read. I hope this posts better.

The report I have to create looks like this:
Transactions Jan Feb etc Total # % of Total
Valid 2200 1849 etc xxxxx xx.x%
Invalid 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

--
susan


"Susan L" wrote:

Thanks for responding. I hope the following will be clearer.

The report I have to create looks like this:
Transactions Jan Feb etc Total #
% of Total
Valid 2200 1849 etc xxxxx
xx.x%
Invalid 2015 1900 etc. xxxxx
xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

The original data that comes to me is structured as:
Date Valid Invalid

I have a crosstab that places the data in rows, and the totals in columns by
month. Because the values are in a single control on the form, though, I
can’t figure out how to get a percentage calculated. The ones on the bottom
row, of course, had only one condition ("Valid"). I tried putting a column in
the xtab to calculate it but got a message that a xtab will only accept one
column.

And, this is the simplest of the calculations I’m facing. I have another
report that has 7 rows and the percentage has to be calculated by the current
month. So I was starting with the “simplest.�

Hope this clarifies. Thanks.

--
susan


"Maverick" wrote:

Susan,

Why are you trying to get two calculations in one field? You either want the
value for valid or value for invalid in one field. Your approach is trying to
say that if Transactions = Valid then give me the percentage of valid and if
Transactions Valid then if Transactions = Invalid then give me the
percentage of invalid. This is an either or calculation.

The reason you are getting the error is because you have too many arguments
for an IIF statement. The syntax is IIF(condition is true, this result, else
this result). The ,0,2 are extraneous and should be removed. I'm not sure
what you are trying to get out of the 0,2 or I would give you an alternative.

If you post a more clear description of what you need, we can possibly
provide you with more useful advice.

HTH

"Susan L" wrote:

I have a crosstab query with string values as row headings (Valid, Invalid)
and months as column headings. In a report, I need to calculate the
percentage of the total for each value. I can get a percentage for either of
the values, but not both. Something is wrong with my syntax.

Here is the expression:
=FormatPercent(IIf([Transactions]="Valid",[txtYTDTotal]/[txtGrandTotal],
IIf([Transactions]="Invalid",[txtYTDTotal]/[txtGrandTotal]),0,2)

The error message says that I have the wrong number of arguments.

Or, if there is a better way to do this, please let me know.
--
susan

Maverick: Had I been replying at midnight, it would have looked like this:
zzzzzzzzz....! Sorry you're burning the midnight oil on my behalf. Have made
some progress with your help.

Here's the report showing just txtJun and txtJul. (The report has all 12
months of data) It's the % of total that I'm trying to get

Function Type Jun Jul etc Total # % of Total
LO1 2200 1849 etc xxxxx xx.x%
CSI 2015 1900 etc. xxxxx xx.x%
Total. xxxx xxxx etc.

The value to divide by is always 2862 but txtJul can have a different number
each month, and txtJul doesn't know it's "7". So using your syntax, I created
a single text box, txtMthVal with a control source of =Month(now())-1.

Then this, which works:

=FormatPercent(IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJun]/2862),2) The "2" controls decimal
places, but I see what you mean -- no if false condition. Don't know why it
works, then.

Because I need to test for 12 months, I tried adding an IIF statement for
June (and changed txtMthVal to "6"), but of course it didn't work. (Tried
several variations) Here's one.

=FormatPercent(IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJun]/2862),IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJul]/2862),2),Null))

I'm sorry to be such a dunce -- fuzzy doesn't describe my condition.


--
susan


"Maverick" wrote:

The idea is to ensure that the control that says last month was July would be
equal to Month(now())-1 which has a value of 7. So, the question is does
txtJul have a numeric value of 7? If the value of txtJul = July x, 2008...
then you should have the formula do the following:

IIF(Month(now())-1=Month(txtJul), txtJul/2862,2)

Furthermore, I think you need to change your And part of the expression. It
should be more like this:

=FormatPercent(IIf([Function Type]="LO1" And (Month(now())-1=Month(txtJul),
txtJul/2862,2)

The way you set it up it was evaluating whether ([Function Type]="LO1" and
the IIF statement about the date were both true. However, it didn't have any
value to establish if they were because the values for the IIF statement were
part of the And. I hope I explained that well. It's midnight here and I'm
getting a little fuzzy. LOL

I'm not sure why you have 2 as the value returned for a false response in
the formula. Typically you'd have it be a null value or 0. Your formula would
result in either the calculation of txtJul/2862 or 2.

In addition, is the value you divide by always going to be 2862? If not, you
should make it so that you can change this number without having to change
the code. If it changes, it would make sense to store the dividend in a table
and have it relate to the month you are calculating.

Let me know if you figure it out or need more help.

"Susan L" wrote:

Maverick: Thanks for "staying the course." I have tried out your suggestion,
but am getting a blank. If you want a copy of the database (it's really small
at this point), I'll be happy to forward.

The text boxes I would be using are those with the IIF statement you
suggested earlier. They have this expression: =FormatPercent(IIf([Function
Type]="LO1", txtJul]/2862),2)). That works. But I don't want to change the
control each month.

So, using your suggestion, I created the following:
=FormatPercent(IIf([Function Type]="LO1" And
IIf(Month(Now())=[txtJul],[txtJul]/2862),2)). This does not produce a value.
Wasn't sure exactly what you meant about the fomatting of the control.

And one more issue, I misspoke about "current" month, as this report is done
afterward. What I need is "prior" month. So do I put a -1 somewhere? Like
(Month(Now()-1)?
--
susan


"Maverick" wrote:

I think it would be a lot easier if I could see a stripped down version of
your database. In looking at what you're showing me, it would seem to be
simple enough to just run an IIF that tests for the current month = the month
in the column. However, I don't know the exact structure of the report so I
can't give specific instructions.

The IIF statement would look something like:
IIF(month(now())=YourControl,YourCalculation,0)

The caveat is that the value of the control should be formatted the same as
the result of month(now())

You could do this for each month and then have one unbound textbox that sums
them or you could build a complex nesting within one control.

I hope this gets you on the right track. If not, just post back.

"Susan L" wrote:

It's a cumulative report, as in the sample, so the totals at the bottom are
there for each month.

But, yes, the percentage to be calculated is only for the current month.
--
susan


"Maverick" wrote:

Are you looking for the report to only report on the current month?

"Susan L" wrote:

Well, in my other reports with 7 rows, the stacking method was effective!!!

Now for the more complicated issue. One of the reports I'm creating has a
percentage that is calculated from the current month's total/a specific
number, e.g., In January, it's the LO1 total for txtJan/2862, in February,
LO1 total for txtFeb/2862. Same thing with CSI, etc.

The reports looks like this only with 7 rows:
Functions Jan Feb etc Total # % of Total Possible
LO1 2200 1849 etc xxxxx xx.x%
CSI 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

Is there a way to write an expression that finds the proper month's column
and performs the calculation? It's the moving to the current month that I'm
stumped on. Any thoughts?

--
susan


"Maverick" wrote:

HTH

Post back if you additional help with the more complicated report. Also, if
you feel that a post answers your question, be sure to click Yes to "Was this
post helpful to you?" as it identifies it as an answered question.

"Susan L" wrote:

Maverick: Thanks for the work around suggestion. I did the calculations in
the two text boxes and then stacked them on top of each other in the row of
controls. This works OK for this report.

For my other calculations, however, this may not be a solution, since there
are seven rows in the queries. I'll give it a try. I may have to admit failur
-- not my usual course of action -- and go back to Excel.

Thanks for your help.
--
susan


"Maverick" wrote:

Susan,

What you can do is create two textboxes. Have on run the IIF statement for
Valid and the other for Invalid. Then you can calculate based on the results.

IIF([Transactions]="Valid",1,0) for one textbox and
IIF([Transactions]="Invalid",1,0) for the other. Now you can count or sum the
total number of each and then perform your percentage calculation based off
of this.


"Susan L" wrote:

That was very hard to read. I hope this posts better.

The report I have to create looks like this:
Transactions Jan Feb etc Total # % of Total
Valid 2200 1849 etc xxxxx xx.x%
Invalid 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

--
susan


"Susan L" wrote:

Thanks for responding. I hope the following will be clearer.

The report I have to create looks like this:
Transactions Jan Feb etc Total #
% of Total
Valid 2200 1849 etc xxxxx
xx.x%
Invalid 2015 1900 etc. xxxxx
xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

The original data that comes to me is structured as:
Date Valid Invalid

I have a crosstab that places the data in rows, and the totals in columns by
month. Because the values are in a single control on the form, though, I
can’t figure out how to get a percentage calculated. The ones on the bottom
row, of course, had only one condition ("Valid"). I tried putting a column in
the xtab to calculate it but got a message that a xtab will only accept one
column.

And, this is the simplest of the calculations I’m facing. I have another
report that has 7 rows and the percentage has to be calculated by the current
month. So I was starting with the “simplest.�

Hope this clarifies. Thanks.

--
susan


"Maverick" wrote:

Susan,

Why are you trying to get two calculations in one field? You either want the
value for valid or value for invalid in one field. Your approach is trying to
say that if Transactions = Valid then give me the percentage of valid and if
Transactions Valid then if Transactions = Invalid then give me the
percentage of invalid. This is an either or calculation.

The reason you are getting the error is because you have too many arguments
for an IIF statement. The syntax is IIF(condition is true, this result, else
this result). The ,0,2 are extraneous and should be removed. I'm not sure
what you are trying to get out of the 0,2 or I would give you an alternative.

If you post a more clear description of what you need, we can possibly
provide you with more useful advice.

HTH

"Susan L" wrote:

I have a crosstab query with string values as row headings (Valid, Invalid)
and months as column headings. In a report, I need to calculate the
percentage of the total for each value. I can get a percentage for either of
the values, but not both. Something is wrong with my syntax.

Here is the expression:
=FormatPercent(IIf([Transactions]="Valid",[txtYTDTotal]/[txtGrandTotal],
IIf([Transactions]="Invalid",[txtYTDTotal]/[txtGrandTotal]),0,2)

The error message says that I have the wrong number of arguments.

Or, if there is a better way to do this, please let me know.
--
susan

1:30 a.m. now. The midnight oil has long burned out. LOL

First, your formula can work without the false condition, but results in a
value that doesn't work with calculations. This means you wouldn't be able to
calculate based on the value of this calculation. This is no big deal if you
won't use it that way. If you want to use this value in a calculation, use
the following:

=FormatPercent(IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJun]/2862,0),2)

I think the problem you're having with the next formula is that the
condition will fail because Month(Now()) does not equal 6. What you stated
was that you only wanted last months' info. So now I'm confused. Do you want
a calculation for each month?

"Susan L" wrote:

Maverick: Had I been replying at midnight, it would have looked like this:
zzzzzzzzz....! Sorry you're burning the midnight oil on my behalf. Have made
some progress with your help.

Here's the report showing just txtJun and txtJul. (The report has all 12
months of data) It's the % of total that I'm trying to get

Function Type Jun Jul etc Total # % of Total
LO1 2200 1849 etc xxxxx xx.x%
CSI 2015 1900 etc. xxxxx xx.x%
Total. xxxx xxxx etc.

The value to divide by is always 2862 but txtJul can have a different number
each month, and txtJul doesn't know it's "7". So using your syntax, I created
a single text box, txtMthVal with a control source of =Month(now())-1.

Then this, which works:

=FormatPercent(IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJun]/2862),2) The "2" controls decimal
places, but I see what you mean -- no if false condition. Don't know why it
works, then.

Because I need to test for 12 months, I tried adding an IIF statement for
June (and changed txtMthVal to "6"), but of course it didn't work. (Tried
several variations) Here's one.

=FormatPercent(IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJun]/2862),IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJul]/2862),2),Null))

I'm sorry to be such a dunce -- fuzzy doesn't describe my condition.


--
susan


"Maverick" wrote:

The idea is to ensure that the control that says last month was July would be
equal to Month(now())-1 which has a value of 7. So, the question is does
txtJul have a numeric value of 7? If the value of txtJul = July x, 2008...
then you should have the formula do the following:

IIF(Month(now())-1=Month(txtJul), txtJul/2862,2)

Furthermore, I think you need to change your And part of the expression. It
should be more like this:

=FormatPercent(IIf([Function Type]="LO1" And (Month(now())-1=Month(txtJul),
txtJul/2862,2)

The way you set it up it was evaluating whether ([Function Type]="LO1" and
the IIF statement about the date were both true. However, it didn't have any
value to establish if they were because the values for the IIF statement were
part of the And. I hope I explained that well. It's midnight here and I'm
getting a little fuzzy. LOL

I'm not sure why you have 2 as the value returned for a false response in
the formula. Typically you'd have it be a null value or 0. Your formula would
result in either the calculation of txtJul/2862 or 2.

In addition, is the value you divide by always going to be 2862? If not, you
should make it so that you can change this number without having to change
the code. If it changes, it would make sense to store the dividend in a table
and have it relate to the month you are calculating.

Let me know if you figure it out or need more help.

"Susan L" wrote:

Maverick: Thanks for "staying the course." I have tried out your suggestion,
but am getting a blank. If you want a copy of the database (it's really small
at this point), I'll be happy to forward.

The text boxes I would be using are those with the IIF statement you
suggested earlier. They have this expression: =FormatPercent(IIf([Function
Type]="LO1", txtJul]/2862),2)). That works. But I don't want to change the
control each month.

So, using your suggestion, I created the following:
=FormatPercent(IIf([Function Type]="LO1" And
IIf(Month(Now())=[txtJul],[txtJul]/2862),2)). This does not produce a value.
Wasn't sure exactly what you meant about the fomatting of the control.

And one more issue, I misspoke about "current" month, as this report is done
afterward. What I need is "prior" month. So do I put a -1 somewhere? Like
(Month(Now()-1)?
--
susan


"Maverick" wrote:

I think it would be a lot easier if I could see a stripped down version of
your database. In looking at what you're showing me, it would seem to be
simple enough to just run an IIF that tests for the current month = the month
in the column. However, I don't know the exact structure of the report so I
can't give specific instructions.

The IIF statement would look something like:
IIF(month(now())=YourControl,YourCalculation,0)

The caveat is that the value of the control should be formatted the same as
the result of month(now())

You could do this for each month and then have one unbound textbox that sums
them or you could build a complex nesting within one control.

I hope this gets you on the right track. If not, just post back.

"Susan L" wrote:

It's a cumulative report, as in the sample, so the totals at the bottom are
there for each month.

But, yes, the percentage to be calculated is only for the current month.
--
susan


"Maverick" wrote:

Are you looking for the report to only report on the current month?

"Susan L" wrote:

Well, in my other reports with 7 rows, the stacking method was effective!!!

Now for the more complicated issue. One of the reports I'm creating has a
percentage that is calculated from the current month's total/a specific
number, e.g., In January, it's the LO1 total for txtJan/2862, in February,
LO1 total for txtFeb/2862. Same thing with CSI, etc.

The reports looks like this only with 7 rows:
Functions Jan Feb etc Total # % of Total Possible
LO1 2200 1849 etc xxxxx xx.x%
CSI 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

Is there a way to write an expression that finds the proper month's column
and performs the calculation? It's the moving to the current month that I'm
stumped on. Any thoughts?

--
susan


"Maverick" wrote:

HTH

Post back if you additional help with the more complicated report. Also, if
you feel that a post answers your question, be sure to click Yes to "Was this
post helpful to you?" as it identifies it as an answered question.

"Susan L" wrote:

Maverick: Thanks for the work around suggestion. I did the calculations in
the two text boxes and then stacked them on top of each other in the row of
controls. This works OK for this report.

For my other calculations, however, this may not be a solution, since there
are seven rows in the queries. I'll give it a try. I may have to admit failur
-- not my usual course of action -- and go back to Excel.

Thanks for your help.
--
susan


"Maverick" wrote:

Susan,

What you can do is create two textboxes. Have on run the IIF statement for
Valid and the other for Invalid. Then you can calculate based on the results.

IIF([Transactions]="Valid",1,0) for one textbox and
IIF([Transactions]="Invalid",1,0) for the other. Now you can count or sum the
total number of each and then perform your percentage calculation based off
of this.


"Susan L" wrote:

That was very hard to read. I hope this posts better.

The report I have to create looks like this:
Transactions Jan Feb etc Total # % of Total
Valid 2200 1849 etc xxxxx xx.x%
Invalid 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

--
susan


"Susan L" wrote:

Thanks for responding. I hope the following will be clearer.

The report I have to create looks like this:
Transactions Jan Feb etc Total #
% of Total
Valid 2200 1849 etc xxxxx
xx.x%
Invalid 2015 1900 etc. xxxxx
xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

The original data that comes to me is structured as:
Date Valid Invalid

I have a crosstab that places the data in rows, and the totals in columns by
month. Because the values are in a single control on the form, though, I
can’t figure out how to get a percentage calculated. The ones on the bottom
row, of course, had only one condition ("Valid"). I tried putting a column in
the xtab to calculate it but got a message that a xtab will only accept one
column.

And, this is the simplest of the calculations I’m facing. I have another
report that has 7 rows and the percentage has to be calculated by the current
month. So I was starting with the “simplest.�

Hope this clarifies. Thanks.

--
susan


"Maverick" wrote:

Susan,

Why are you trying to get two calculations in one field? You either want the
value for valid or value for invalid in one field. Your approach is trying to
say that if Transactions = Valid then give me the percentage of valid and if
Transactions Valid then if Transactions = Invalid then give me the
percentage of invalid. This is an either or calculation.

The reason you are getting the error is because you have too many arguments
for an IIF statement. The syntax is IIF(condition is true, this result, else
this result). The ,0,2 are extraneous and should be removed. I'm not sure
what you are trying to get out of the 0,2 or I would give you an alternative.

If you post a more clear description of what you need, we can possibly
provide you with more useful advice.

HTH

"Susan L" wrote:

I have a crosstab query with string values as row headings (Valid, Invalid)
and months as column headings. In a report, I need to calculate the
percentage of the total for each value. I can get a percentage for either of
the values, but not both. Something is wrong with my syntax.

Here is the expression:
=FormatPercent(IIf([Transactions]="Valid",[txtYTDTotal]/[txtGrandTotal],
IIf([Transactions]="Invalid",[txtYTDTotal]/[txtGrandTotal]),0,2)

The error message says that I have the wrong number of arguments.

Or, if there is a better way to do this, please let me know.
--
susan

Sounds like I need to send you some whale oil for your lamp!
LOL.(hmmm...wonder where I get whale oil?...My sister lives in Marblehead, MA
-- maybe there's some left there from the old days.)

Sorry about the confusion. This is a cumulative report that shows data from
Jan-Dec, once we get to Dec. Each month, a new percentage must be calculated;
if it's now July's report, the calculation is txtJul data/2862.; if August,
txtAug/2862. (Not used in any other calculations, but your syntax defenitely
worth filing away!) The report is prepared by the 5th working day of the
month following the month to be reported. This is why the month function
seems so appropriate. In short, the percentage is a "rolling" calculation
revised each month as new data is available. HTH

This is why I need to test for a value for each month of the year. What I'm
going to do is temporarily take out the MonthNow() function and manually
enter the month number. in the MthVal control, so I can test for more than
one month to see if the syntax is right.

I'll do that tomorrow am and see how I do. I'll post back (I hope saying the
problem is solved. You've been great.) Thanks so much

--
susan


"Maverick" wrote:

1:30 a.m. now. The midnight oil has long burned out. LOL

First, your formula can work without the false condition, but results in a
value that doesn't work with calculations. This means you wouldn't be able to
calculate based on the value of this calculation. This is no big deal if you
won't use it that way. If you want to use this value in a calculation, use
the following:

=FormatPercent(IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJun]/2862,0),2)

I think the problem you're having with the next formula is that the
condition will fail because Month(Now()) does not equal 6. What you stated
was that you only wanted last months' info. So now I'm confused. Do you want
a calculation for each month?

"Susan L" wrote:

Maverick: Had I been replying at midnight, it would have looked like this:
zzzzzzzzz....! Sorry you're burning the midnight oil on my behalf. Have made
some progress with your help.

Here's the report showing just txtJun and txtJul. (The report has all 12
months of data) It's the % of total that I'm trying to get

Function Type Jun Jul etc Total # % of Total
LO1 2200 1849 etc xxxxx xx.x%
CSI 2015 1900 etc. xxxxx xx.x%
Total. xxxx xxxx etc.

The value to divide by is always 2862 but txtJul can have a different number
each month, and txtJul doesn't know it's "7". So using your syntax, I created
a single text box, txtMthVal with a control source of =Month(now())-1.

Then this, which works:

=FormatPercent(IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJun]/2862),2) The "2" controls decimal
places, but I see what you mean -- no if false condition. Don't know why it
works, then.

Because I need to test for 12 months, I tried adding an IIF statement for
June (and changed txtMthVal to "6"), but of course it didn't work. (Tried
several variations) Here's one.

=FormatPercent(IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJun]/2862),IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJul]/2862),2),Null))

I'm sorry to be such a dunce -- fuzzy doesn't describe my condition.


--
susan


"Maverick" wrote:

The idea is to ensure that the control that says last month was July would be
equal to Month(now())-1 which has a value of 7. So, the question is does
txtJul have a numeric value of 7? If the value of txtJul = July x, 2008...
then you should have the formula do the following:

IIF(Month(now())-1=Month(txtJul), txtJul/2862,2)

Furthermore, I think you need to change your And part of the expression. It
should be more like this:

=FormatPercent(IIf([Function Type]="LO1" And (Month(now())-1=Month(txtJul),
txtJul/2862,2)

The way you set it up it was evaluating whether ([Function Type]="LO1" and
the IIF statement about the date were both true. However, it didn't have any
value to establish if they were because the values for the IIF statement were
part of the And. I hope I explained that well. It's midnight here and I'm
getting a little fuzzy. LOL

I'm not sure why you have 2 as the value returned for a false response in
the formula. Typically you'd have it be a null value or 0. Your formula would
result in either the calculation of txtJul/2862 or 2.

In addition, is the value you divide by always going to be 2862? If not, you
should make it so that you can change this number without having to change
the code. If it changes, it would make sense to store the dividend in a table
and have it relate to the month you are calculating.

Let me know if you figure it out or need more help.

"Susan L" wrote:

Maverick: Thanks for "staying the course." I have tried out your suggestion,
but am getting a blank. If you want a copy of the database (it's really small
at this point), I'll be happy to forward.

The text boxes I would be using are those with the IIF statement you
suggested earlier. They have this expression: =FormatPercent(IIf([Function
Type]="LO1", txtJul]/2862),2)). That works. But I don't want to change the
control each month.

So, using your suggestion, I created the following:
=FormatPercent(IIf([Function Type]="LO1" And
IIf(Month(Now())=[txtJul],[txtJul]/2862),2)). This does not produce a value.
Wasn't sure exactly what you meant about the fomatting of the control.

And one more issue, I misspoke about "current" month, as this report is done
afterward. What I need is "prior" month. So do I put a -1 somewhere? Like
(Month(Now()-1)?
--
susan


"Maverick" wrote:

I think it would be a lot easier if I could see a stripped down version of
your database. In looking at what you're showing me, it would seem to be
simple enough to just run an IIF that tests for the current month = the month
in the column. However, I don't know the exact structure of the report so I
can't give specific instructions.

The IIF statement would look something like:
IIF(month(now())=YourControl,YourCalculation,0)

The caveat is that the value of the control should be formatted the same as
the result of month(now())

You could do this for each month and then have one unbound textbox that sums
them or you could build a complex nesting within one control.

I hope this gets you on the right track. If not, just post back.

"Susan L" wrote:

It's a cumulative report, as in the sample, so the totals at the bottom are
there for each month.

But, yes, the percentage to be calculated is only for the current month.
--
susan


"Maverick" wrote:

Are you looking for the report to only report on the current month?

"Susan L" wrote:

Well, in my other reports with 7 rows, the stacking method was effective!!!

Now for the more complicated issue. One of the reports I'm creating has a
percentage that is calculated from the current month's total/a specific
number, e.g., In January, it's the LO1 total for txtJan/2862, in February,
LO1 total for txtFeb/2862. Same thing with CSI, etc.

The reports looks like this only with 7 rows:
Functions Jan Feb etc Total # % of Total Possible
LO1 2200 1849 etc xxxxx xx.x%
CSI 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

Is there a way to write an expression that finds the proper month's column
and performs the calculation? It's the moving to the current month that I'm
stumped on. Any thoughts?

--
susan


"Maverick" wrote:

HTH

Post back if you additional help with the more complicated report. Also, if
you feel that a post answers your question, be sure to click Yes to "Was this
post helpful to you?" as it identifies it as an answered question.

"Susan L" wrote:

Maverick: Thanks for the work around suggestion. I did the calculations in
the two text boxes and then stacked them on top of each other in the row of
controls. This works OK for this report.

For my other calculations, however, this may not be a solution, since there
are seven rows in the queries. I'll give it a try. I may have to admit failur
-- not my usual course of action -- and go back to Excel.

Thanks for your help.
--
susan


"Maverick" wrote:

Susan,

What you can do is create two textboxes. Have on run the IIF statement for
Valid and the other for Invalid. Then you can calculate based on the results.

IIF([Transactions]="Valid",1,0) for one textbox and
IIF([Transactions]="Invalid",1,0) for the other. Now you can count or sum the
total number of each and then perform your percentage calculation based off
of this.


"Susan L" wrote:

That was very hard to read. I hope this posts better.

The report I have to create looks like this:
Transactions Jan Feb etc Total # % of Total
Valid 2200 1849 etc xxxxx xx.x%
Invalid 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

--
susan


"Susan L" wrote:

Thanks for responding. I hope the following will be clearer.

The report I have to create looks like this:
Transactions Jan Feb etc Total #
% of Total
Valid 2200 1849 etc xxxxx
xx.x%
Invalid 2015 1900 etc. xxxxx
xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

The original data that comes to me is structured as:
Date Valid Invalid

I have a crosstab that places the data in rows, and the totals in columns by
month. Because the values are in a single control on the form, though, I
can’t figure out how to get a percentage calculated. The ones on the bottom
row, of course, had only one condition ("Valid"). I tried putting a column in
the xtab to calculate it but got a message that a xtab will only accept one
column.

And, this is the simplest of the calculations I’m facing. I have another
report that has 7 rows and the percentage has to be calculated by the current
month. So I was starting with the “simplest.�

Hope this clarifies. Thanks.

--
susan


"Maverick" wrote:

Susan,

Why are you trying to get two calculations in one field? You either want the
value for valid or value for invalid in one field. Your approach is trying to
say that if Transactions = Valid then give me the percentage of valid and if
Transactions Valid then if Transactions = Invalid then give me the
percentage of invalid. This is an either or calculation.

The reason you are getting the error is because you have too many arguments
for an IIF statement. The syntax is IIF(condition is true, this result, else
this result). The ,0,2 are extraneous and should be removed. I'm not sure
what you are trying to get out of the 0,2 or I would give you an alternative.

If you post a more clear description of what you need, we can possibly
provide you with more useful advice.

HTH

"Susan L" wrote:

I have a crosstab query with string values as row headings (Valid, Invalid)
and months as column headings. In a report, I need to calculate the
percentage of the total for each value. I can get a percentage for either of
the values, but not both. Something is wrong with my syntax.

Here is the expression:
=FormatPercent(IIf([Transactions]="Valid",[txtYTDTotal]/[txtGrandTotal],
IIf([Transactions]="Invalid",[txtYTDTotal]/[txtGrandTotal]),0,2)

The error message says that I have the wrong number of arguments.

Or, if there is a better way to do this, please let me know.
--
susan

Did the formula work?

"Susan L" wrote:

Sounds like I need to send you some whale oil for your lamp!
LOL.(hmmm...wonder where I get whale oil?...My sister lives in Marblehead, MA
-- maybe there's some left there from the old days.)

Sorry about the confusion. This is a cumulative report that shows data from
Jan-Dec, once we get to Dec. Each month, a new percentage must be calculated;
if it's now July's report, the calculation is txtJul data/2862.; if August,
txtAug/2862. (Not used in any other calculations, but your syntax defenitely
worth filing away!) The report is prepared by the 5th working day of the
month following the month to be reported. This is why the month function
seems so appropriate. In short, the percentage is a "rolling" calculation
revised each month as new data is available. HTH

This is why I need to test for a value for each month of the year. What I'm
going to do is temporarily take out the MonthNow() function and manually
enter the month number. in the MthVal control, so I can test for more than
one month to see if the syntax is right.

I'll do that tomorrow am and see how I do. I'll post back (I hope saying the
problem is solved. You've been great.) Thanks so much

--
susan


"Maverick" wrote:

1:30 a.m. now. The midnight oil has long burned out. LOL

First, your formula can work without the false condition, but results in a
value that doesn't work with calculations. This means you wouldn't be able to
calculate based on the value of this calculation. This is no big deal if you
won't use it that way. If you want to use this value in a calculation, use
the following:

=FormatPercent(IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJun]/2862,0),2)

I think the problem you're having with the next formula is that the
condition will fail because Month(Now()) does not equal 6. What you stated
was that you only wanted last months' info. So now I'm confused. Do you want
a calculation for each month?

"Susan L" wrote:

Maverick: Had I been replying at midnight, it would have looked like this:
zzzzzzzzz....! Sorry you're burning the midnight oil on my behalf. Have made
some progress with your help.

Here's the report showing just txtJun and txtJul. (The report has all 12
months of data) It's the % of total that I'm trying to get

Function Type Jun Jul etc Total # % of Total
LO1 2200 1849 etc xxxxx xx.x%
CSI 2015 1900 etc. xxxxx xx.x%
Total. xxxx xxxx etc.

The value to divide by is always 2862 but txtJul can have a different number
each month, and txtJul doesn't know it's "7". So using your syntax, I created
a single text box, txtMthVal with a control source of =Month(now())-1.

Then this, which works:

=FormatPercent(IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJun]/2862),2) The "2" controls decimal
places, but I see what you mean -- no if false condition. Don't know why it
works, then.

Because I need to test for 12 months, I tried adding an IIF statement for
June (and changed txtMthVal to "6"), but of course it didn't work. (Tried
several variations) Here's one.

=FormatPercent(IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJun]/2862),IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJul]/2862),2),Null))

I'm sorry to be such a dunce -- fuzzy doesn't describe my condition.


--
susan


"Maverick" wrote:

The idea is to ensure that the control that says last month was July would be
equal to Month(now())-1 which has a value of 7. So, the question is does
txtJul have a numeric value of 7? If the value of txtJul = July x, 2008...
then you should have the formula do the following:

IIF(Month(now())-1=Month(txtJul), txtJul/2862,2)

Furthermore, I think you need to change your And part of the expression. It
should be more like this:

=FormatPercent(IIf([Function Type]="LO1" And (Month(now())-1=Month(txtJul),
txtJul/2862,2)

The way you set it up it was evaluating whether ([Function Type]="LO1" and
the IIF statement about the date were both true. However, it didn't have any
value to establish if they were because the values for the IIF statement were
part of the And. I hope I explained that well. It's midnight here and I'm
getting a little fuzzy. LOL

I'm not sure why you have 2 as the value returned for a false response in
the formula. Typically you'd have it be a null value or 0. Your formula would
result in either the calculation of txtJul/2862 or 2.

In addition, is the value you divide by always going to be 2862? If not, you
should make it so that you can change this number without having to change
the code. If it changes, it would make sense to store the dividend in a table
and have it relate to the month you are calculating.

Let me know if you figure it out or need more help.

"Susan L" wrote:

Maverick: Thanks for "staying the course." I have tried out your suggestion,
but am getting a blank. If you want a copy of the database (it's really small
at this point), I'll be happy to forward.

The text boxes I would be using are those with the IIF statement you
suggested earlier. They have this expression: =FormatPercent(IIf([Function
Type]="LO1", txtJul]/2862),2)). That works. But I don't want to change the
control each month.

So, using your suggestion, I created the following:
=FormatPercent(IIf([Function Type]="LO1" And
IIf(Month(Now())=[txtJul],[txtJul]/2862),2)). This does not produce a value.
Wasn't sure exactly what you meant about the fomatting of the control.

And one more issue, I misspoke about "current" month, as this report is done
afterward. What I need is "prior" month. So do I put a -1 somewhere? Like
(Month(Now()-1)?
--
susan


"Maverick" wrote:

I think it would be a lot easier if I could see a stripped down version of
your database. In looking at what you're showing me, it would seem to be
simple enough to just run an IIF that tests for the current month = the month
in the column. However, I don't know the exact structure of the report so I
can't give specific instructions.

The IIF statement would look something like:
IIF(month(now())=YourControl,YourCalculation,0)

The caveat is that the value of the control should be formatted the same as
the result of month(now())

You could do this for each month and then have one unbound textbox that sums
them or you could build a complex nesting within one control.

I hope this gets you on the right track. If not, just post back.

"Susan L" wrote:

It's a cumulative report, as in the sample, so the totals at the bottom are
there for each month.

But, yes, the percentage to be calculated is only for the current month.
--
susan


"Maverick" wrote:

Are you looking for the report to only report on the current month?

"Susan L" wrote:

Well, in my other reports with 7 rows, the stacking method was effective!!!

Now for the more complicated issue. One of the reports I'm creating has a
percentage that is calculated from the current month's total/a specific
number, e.g., In January, it's the LO1 total for txtJan/2862, in February,
LO1 total for txtFeb/2862. Same thing with CSI, etc.

The reports looks like this only with 7 rows:
Functions Jan Feb etc Total # % of Total Possible
LO1 2200 1849 etc xxxxx xx.x%
CSI 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

Is there a way to write an expression that finds the proper month's column
and performs the calculation? It's the moving to the current month that I'm
stumped on. Any thoughts?

--
susan


"Maverick" wrote:

HTH

Post back if you additional help with the more complicated report. Also, if
you feel that a post answers your question, be sure to click Yes to "Was this
post helpful to you?" as it identifies it as an answered question.

"Susan L" wrote:

Maverick: Thanks for the work around suggestion. I did the calculations in
the two text boxes and then stacked them on top of each other in the row of
controls. This works OK for this report.

For my other calculations, however, this may not be a solution, since there
are seven rows in the queries. I'll give it a try. I may have to admit failur
-- not my usual course of action -- and go back to Excel.

Thanks for your help.
--
susan


"Maverick" wrote:

Susan,

What you can do is create two textboxes. Have on run the IIF statement for
Valid and the other for Invalid. Then you can calculate based on the results.

IIF([Transactions]="Valid",1,0) for one textbox and
IIF([Transactions]="Invalid",1,0) for the other. Now you can count or sum the
total number of each and then perform your percentage calculation based off
of this.


"Susan L" wrote:

That was very hard to read. I hope this posts better.

The report I have to create looks like this:
Transactions Jan Feb etc Total # % of Total
Valid 2200 1849 etc xxxxx xx.x%
Invalid 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

--
susan


"Susan L" wrote:

Thanks for responding. I hope the following will be clearer.

The report I have to create looks like this:
Transactions Jan Feb etc Total #
% of Total
Valid 2200 1849 etc xxxxx
xx.x%
Invalid 2015 1900 etc. xxxxx
xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

The original data that comes to me is structured as:
Date Valid Invalid

I have a crosstab that places the data in rows, and the totals in columns by
month. Because the values are in a single control on the form, though, I
can’t figure out how to get a percentage calculated. The ones on the bottom
row, of course, had only one condition ("Valid"). I tried putting a column in
the xtab to calculate it but got a message that a xtab will only accept one
column.

And, this is the simplest of the calculations I’m facing. I have another
report that has 7 rows and the percentage has to be calculated by the current
month. So I was starting with the “simplest.�

Hope this clarifies. Thanks.

--
susan


"Maverick" wrote:

Susan,

Why are you trying to get two calculations in one field? You either want the
value for valid or value for invalid in one field. Your approach is trying to
say that if Transactions = Valid then give me the percentage of valid and if
Transactions Valid then if Transactions = Invalid then give me the
percentage of invalid. This is an either or calculation.

The reason you are getting the error is because you have too many arguments
for an IIF statement. The syntax is IIF(condition is true, this result, else
this result). The ,0,2 are extraneous and should be removed. I'm not sure
what you are trying to get out of the 0,2 or I would give you an alternative.

If you post a more clear description of what you need, we can possibly
provide you with more useful advice.

HTH

Best laid plans...was put on another project. Am just getting back to this as
we "speak". Will post back.
--
susan


"Maverick" wrote:

Did the formula work?

"Susan L" wrote:

Sounds like I need to send you some whale oil for your lamp!
LOL.(hmmm...wonder where I get whale oil?...My sister lives in Marblehead, MA
-- maybe there's some left there from the old days.)

Sorry about the confusion. This is a cumulative report that shows data from
Jan-Dec, once we get to Dec. Each month, a new percentage must be calculated;
if it's now July's report, the calculation is txtJul data/2862.; if August,
txtAug/2862. (Not used in any other calculations, but your syntax defenitely
worth filing away!) The report is prepared by the 5th working day of the
month following the month to be reported. This is why the month function
seems so appropriate. In short, the percentage is a "rolling" calculation
revised each month as new data is available. HTH

This is why I need to test for a value for each month of the year. What I'm
going to do is temporarily take out the MonthNow() function and manually
enter the month number. in the MthVal control, so I can test for more than
one month to see if the syntax is right.

I'll do that tomorrow am and see how I do. I'll post back (I hope saying the
problem is solved. You've been great.) Thanks so much

--
susan


"Maverick" wrote:

1:30 a.m. now. The midnight oil has long burned out. LOL

First, your formula can work without the false condition, but results in a
value that doesn't work with calculations. This means you wouldn't be able to
calculate based on the value of this calculation. This is no big deal if you
won't use it that way. If you want to use this value in a calculation, use
the following:

=FormatPercent(IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJun]/2862,0),2)

I think the problem you're having with the next formula is that the
condition will fail because Month(Now()) does not equal 6. What you stated
was that you only wanted last months' info. So now I'm confused. Do you want
a calculation for each month?

"Susan L" wrote:

Maverick: Had I been replying at midnight, it would have looked like this:
zzzzzzzzz....! Sorry you're burning the midnight oil on my behalf. Have made
some progress with your help.

Here's the report showing just txtJun and txtJul. (The report has all 12
months of data) It's the % of total that I'm trying to get

Function Type Jun Jul etc Total # % of Total
LO1 2200 1849 etc xxxxx xx.x%
CSI 2015 1900 etc. xxxxx xx.x%
Total. xxxx xxxx etc.

The value to divide by is always 2862 but txtJul can have a different number
each month, and txtJul doesn't know it's "7". So using your syntax, I created
a single text box, txtMthVal with a control source of =Month(now())-1.

Then this, which works:

=FormatPercent(IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJun]/2862),2) The "2" controls decimal
places, but I see what you mean -- no if false condition. Don't know why it
works, then.

Because I need to test for 12 months, I tried adding an IIF statement for
June (and changed txtMthVal to "6"), but of course it didn't work. (Tried
several variations) Here's one.

=FormatPercent(IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJun]/2862),IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJul]/2862),2),Null))

I'm sorry to be such a dunce -- fuzzy doesn't describe my condition.


--
susan


"Maverick" wrote:

The idea is to ensure that the control that says last month was July would be
equal to Month(now())-1 which has a value of 7. So, the question is does
txtJul have a numeric value of 7? If the value of txtJul = July x, 2008...
then you should have the formula do the following:

IIF(Month(now())-1=Month(txtJul), txtJul/2862,2)

Furthermore, I think you need to change your And part of the expression. It
should be more like this:

=FormatPercent(IIf([Function Type]="LO1" And (Month(now())-1=Month(txtJul),
txtJul/2862,2)

The way you set it up it was evaluating whether ([Function Type]="LO1" and
the IIF statement about the date were both true. However, it didn't have any
value to establish if they were because the values for the IIF statement were
part of the And. I hope I explained that well. It's midnight here and I'm
getting a little fuzzy. LOL

I'm not sure why you have 2 as the value returned for a false response in
the formula. Typically you'd have it be a null value or 0. Your formula would
result in either the calculation of txtJul/2862 or 2.

In addition, is the value you divide by always going to be 2862? If not, you
should make it so that you can change this number without having to change
the code. If it changes, it would make sense to store the dividend in a table
and have it relate to the month you are calculating.

Let me know if you figure it out or need more help.

"Susan L" wrote:

Maverick: Thanks for "staying the course." I have tried out your suggestion,
but am getting a blank. If you want a copy of the database (it's really small
at this point), I'll be happy to forward.

The text boxes I would be using are those with the IIF statement you
suggested earlier. They have this expression: =FormatPercent(IIf([Function
Type]="LO1", txtJul]/2862),2)). That works. But I don't want to change the
control each month.

So, using your suggestion, I created the following:
=FormatPercent(IIf([Function Type]="LO1" And
IIf(Month(Now())=[txtJul],[txtJul]/2862),2)). This does not produce a value.
Wasn't sure exactly what you meant about the fomatting of the control.

And one more issue, I misspoke about "current" month, as this report is done
afterward. What I need is "prior" month. So do I put a -1 somewhere? Like
(Month(Now()-1)?
--
susan


"Maverick" wrote:

I think it would be a lot easier if I could see a stripped down version of
your database. In looking at what you're showing me, it would seem to be
simple enough to just run an IIF that tests for the current month = the month
in the column. However, I don't know the exact structure of the report so I
can't give specific instructions.

The IIF statement would look something like:
IIF(month(now())=YourControl,YourCalculation,0)

The caveat is that the value of the control should be formatted the same as
the result of month(now())

You could do this for each month and then have one unbound textbox that sums
them or you could build a complex nesting within one control.

I hope this gets you on the right track. If not, just post back.

"Susan L" wrote:

It's a cumulative report, as in the sample, so the totals at the bottom are
there for each month.

But, yes, the percentage to be calculated is only for the current month.
--
susan


"Maverick" wrote:

Are you looking for the report to only report on the current month?

"Susan L" wrote:

Well, in my other reports with 7 rows, the stacking method was effective!!!

Now for the more complicated issue. One of the reports I'm creating has a
percentage that is calculated from the current month's total/a specific
number, e.g., In January, it's the LO1 total for txtJan/2862, in February,
LO1 total for txtFeb/2862. Same thing with CSI, etc.

The reports looks like this only with 7 rows:
Functions Jan Feb etc Total # % of Total Possible
LO1 2200 1849 etc xxxxx xx.x%
CSI 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

Is there a way to write an expression that finds the proper month's column
and performs the calculation? It's the moving to the current month that I'm
stumped on. Any thoughts?

--
susan


"Maverick" wrote:

HTH

Post back if you additional help with the more complicated report. Also, if
you feel that a post answers your question, be sure to click Yes to "Was this
post helpful to you?" as it identifies it as an answered question.

"Susan L" wrote:

Maverick: Thanks for the work around suggestion. I did the calculations in
the two text boxes and then stacked them on top of each other in the row of
controls. This works OK for this report.

For my other calculations, however, this may not be a solution, since there
are seven rows in the queries. I'll give it a try. I may have to admit failur
-- not my usual course of action -- and go back to Excel.

Thanks for your help.
--
susan


"Maverick" wrote:

Susan,

What you can do is create two textboxes. Have on run the IIF statement for
Valid and the other for Invalid. Then you can calculate based on the results.

IIF([Transactions]="Valid",1,0) for one textbox and
IIF([Transactions]="Invalid",1,0) for the other. Now you can count or sum the
total number of each and then perform your percentage calculation based off
of this.


"Susan L" wrote:

That was very hard to read. I hope this posts better.

The report I have to create looks like this:
Transactions Jan Feb etc Total # % of Total
Valid 2200 1849 etc xxxxx xx.x%
Invalid 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

--
susan


"Susan L" wrote:

Thanks for responding. I hope the following will be clearer.

The report I have to create looks like this:
Transactions Jan Feb etc Total #
% of Total
Valid 2200 1849 etc xxxxx
xx.x%
Invalid 2015 1900 etc. xxxxx
xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

The original data that comes to me is structured as:
Date Valid Invalid

I have a crosstab that places the data in rows, and the totals in columns by
month. Because the values are in a single control on the form, though, I
can’t figure out how to get a percentage calculated. The ones on the bottom
row, of course, had only one condition ("Valid"). I tried putting a column in
the xtab to calculate it but got a message that a xtab will only accept one
column.

And, this is the simplest of the calculations I’m facing. I have another
report that has 7 rows and the percentage has to be calculated by the current
month. So I was starting with the “simplest.�

Hope this clarifies. Thanks.

--
susan


"Maverick" wrote:

Susan,

Why are you trying to get two calculations in one field? You either want the
value for valid or value for invalid in one field. Your approach is trying to
say that if Transactions = Valid then give me the percentage of valid and if
Transactions Valid then if Transactions = Invalid then give me the
percentage of invalid. This is an either or calculation.

The reason you are getting the error is because you have too many arguments
for an IIF statement. The syntax is IIF(condition is true, this result, else
this result). The ,0,2 are extraneous and should be removed. I'm not sure
what you are trying to get out of the 0,2 or I would give you an alternative.

If you post a more clear description of what you need, we can possibly

Maverick: Am still having difficulty.

Have made a change: rather than put the Month(Now() in the calculation, it
will be simpler to make Month(Now() the control source of my new text field,
txtMthVal, then have the formula look up the month value there.

Then in the formula, state that when the value of txtMthVal = 6, for
example, calculate txtJun/2862; when 7, calculte txtJul/2862 etc. (Eventually
the calculation needs to encompass all 12 months.)

For now, for testing purposes, I am simply typing an integer into txtMthVal
and am not using the Month(Now() for the control source.

So here's what I put together. The first statement works when the txtMthVal
= 6, but when txtMthVal = 7, there is no result.

=FormatPercent(IIf([Function Type]="LO1" And
([txtMthVal]=6),[txtJun]/2862,(IIf([Function Type]="LO1" And
([txtMthVal]=7),[txtJul]/2862,0),2)))

Any further thoughts?

susan


"Susan L" wrote:

Best laid plans...was put on another project. Am just getting back to this as
we "speak". Will post back.
--
susan


"Maverick" wrote:

Did the formula work?

"Susan L" wrote:

Sounds like I need to send you some whale oil for your lamp!
LOL.(hmmm...wonder where I get whale oil?...My sister lives in Marblehead, MA
-- maybe there's some left there from the old days.)

Sorry about the confusion. This is a cumulative report that shows data from
Jan-Dec, once we get to Dec. Each month, a new percentage must be calculated;
if it's now July's report, the calculation is txtJul data/2862.; if August,
txtAug/2862. (Not used in any other calculations, but your syntax defenitely
worth filing away!) The report is prepared by the 5th working day of the
month following the month to be reported. This is why the month function
seems so appropriate. In short, the percentage is a "rolling" calculation
revised each month as new data is available. HTH

This is why I need to test for a value for each month of the year. What I'm
going to do is temporarily take out the MonthNow() function and manually
enter the month number. in the MthVal control, so I can test for more than
one month to see if the syntax is right.

I'll do that tomorrow am and see how I do. I'll post back (I hope saying the
problem is solved. You've been great.) Thanks so much

--
susan


"Maverick" wrote:

1:30 a.m. now. The midnight oil has long burned out. LOL

First, your formula can work without the false condition, but results in a
value that doesn't work with calculations. This means you wouldn't be able to
calculate based on the value of this calculation. This is no big deal if you
won't use it that way. If you want to use this value in a calculation, use
the following:

=FormatPercent(IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJun]/2862,0),2)

I think the problem you're having with the next formula is that the
condition will fail because Month(Now()) does not equal 6. What you stated
was that you only wanted last months' info. So now I'm confused. Do you want
a calculation for each month?

"Susan L" wrote:

Maverick: Had I been replying at midnight, it would have looked like this:
zzzzzzzzz....! Sorry you're burning the midnight oil on my behalf. Have made
some progress with your help.

Here's the report showing just txtJun and txtJul. (The report has all 12
months of data) It's the % of total that I'm trying to get

Function Type Jun Jul etc Total # % of Total
LO1 2200 1849 etc xxxxx xx.x%
CSI 2015 1900 etc. xxxxx xx.x%
Total. xxxx xxxx etc.

The value to divide by is always 2862 but txtJul can have a different number
each month, and txtJul doesn't know it's "7". So using your syntax, I created
a single text box, txtMthVal with a control source of =Month(now())-1.

Then this, which works:

=FormatPercent(IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJun]/2862),2) The "2" controls decimal
places, but I see what you mean -- no if false condition. Don't know why it
works, then.

Because I need to test for 12 months, I tried adding an IIF statement for
June (and changed txtMthVal to "6"), but of course it didn't work. (Tried
several variations) Here's one.

=FormatPercent(IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJun]/2862),IIf([Function Type]="LO1" And
(Month(Now())-1=[txtMthVal]),[txtJul]/2862),2),Null))

I'm sorry to be such a dunce -- fuzzy doesn't describe my condition.


--
susan


"Maverick" wrote:

The idea is to ensure that the control that says last month was July would be
equal to Month(now())-1 which has a value of 7. So, the question is does
txtJul have a numeric value of 7? If the value of txtJul = July x, 2008...
then you should have the formula do the following:

IIF(Month(now())-1=Month(txtJul), txtJul/2862,2)

Furthermore, I think you need to change your And part of the expression. It
should be more like this:

=FormatPercent(IIf([Function Type]="LO1" And (Month(now())-1=Month(txtJul),
txtJul/2862,2)

The way you set it up it was evaluating whether ([Function Type]="LO1" and
the IIF statement about the date were both true. However, it didn't have any
value to establish if they were because the values for the IIF statement were
part of the And. I hope I explained that well. It's midnight here and I'm
getting a little fuzzy. LOL

I'm not sure why you have 2 as the value returned for a false response in
the formula. Typically you'd have it be a null value or 0. Your formula would
result in either the calculation of txtJul/2862 or 2.

In addition, is the value you divide by always going to be 2862? If not, you
should make it so that you can change this number without having to change
the code. If it changes, it would make sense to store the dividend in a table
and have it relate to the month you are calculating.

Let me know if you figure it out or need more help.

"Susan L" wrote:

Maverick: Thanks for "staying the course." I have tried out your suggestion,
but am getting a blank. If you want a copy of the database (it's really small
at this point), I'll be happy to forward.

The text boxes I would be using are those with the IIF statement you
suggested earlier. They have this expression: =FormatPercent(IIf([Function
Type]="LO1", txtJul]/2862),2)). That works. But I don't want to change the
control each month.

So, using your suggestion, I created the following:
=FormatPercent(IIf([Function Type]="LO1" And
IIf(Month(Now())=[txtJul],[txtJul]/2862),2)). This does not produce a value.
Wasn't sure exactly what you meant about the fomatting of the control.

And one more issue, I misspoke about "current" month, as this report is done
afterward. What I need is "prior" month. So do I put a -1 somewhere? Like
(Month(Now()-1)?
--
susan


"Maverick" wrote:

I think it would be a lot easier if I could see a stripped down version of
your database. In looking at what you're showing me, it would seem to be
simple enough to just run an IIF that tests for the current month = the month
in the column. However, I don't know the exact structure of the report so I
can't give specific instructions.

The IIF statement would look something like:
IIF(month(now())=YourControl,YourCalculation,0)

The caveat is that the value of the control should be formatted the same as
the result of month(now())

You could do this for each month and then have one unbound textbox that sums
them or you could build a complex nesting within one control.

I hope this gets you on the right track. If not, just post back.

"Susan L" wrote:

It's a cumulative report, as in the sample, so the totals at the bottom are
there for each month.

But, yes, the percentage to be calculated is only for the current month.
--
susan


"Maverick" wrote:

Are you looking for the report to only report on the current month?

"Susan L" wrote:

Well, in my other reports with 7 rows, the stacking method was effective!!!

Now for the more complicated issue. One of the reports I'm creating has a
percentage that is calculated from the current month's total/a specific
number, e.g., In January, it's the LO1 total for txtJan/2862, in February,
LO1 total for txtFeb/2862. Same thing with CSI, etc.

The reports looks like this only with 7 rows:
Functions Jan Feb etc Total # % of Total Possible
LO1 2200 1849 etc xxxxx xx.x%
CSI 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

Is there a way to write an expression that finds the proper month's column
and performs the calculation? It's the moving to the current month that I'm
stumped on. Any thoughts?

--
susan


"Maverick" wrote:

HTH

Post back if you additional help with the more complicated report. Also, if
you feel that a post answers your question, be sure to click Yes to "Was this
post helpful to you?" as it identifies it as an answered question.

"Susan L" wrote:

Maverick: Thanks for the work around suggestion. I did the calculations in
the two text boxes and then stacked them on top of each other in the row of
controls. This works OK for this report.

For my other calculations, however, this may not be a solution, since there
are seven rows in the queries. I'll give it a try. I may have to admit failur
-- not my usual course of action -- and go back to Excel.

Thanks for your help.
--
susan


"Maverick" wrote:

Susan,

What you can do is create two textboxes. Have on run the IIF statement for
Valid and the other for Invalid. Then you can calculate based on the results.

IIF([Transactions]="Valid",1,0) for one textbox and
IIF([Transactions]="Invalid",1,0) for the other. Now you can count or sum the
total number of each and then perform your percentage calculation based off
of this.


"Susan L" wrote:

That was very hard to read. I hope this posts better.

The report I have to create looks like this:
Transactions Jan Feb etc Total # % of Total
Valid 2200 1849 etc xxxxx xx.x%
Invalid 2015 1900 etc. xxxxx xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

--
susan


"Susan L" wrote:

Thanks for responding. I hope the following will be clearer.

The report I have to create looks like this:
Transactions Jan Feb etc Total #
% of Total
Valid 2200 1849 etc xxxxx
xx.x%
Invalid 2015 1900 etc. xxxxx
xx.x%
Total/Mo. xxxx xxxx etc.
% /Valid/Mo. xx.x% xx.x% etc

The original data that comes to me is structured as:
Date Valid Invalid

I have a crosstab that places the data in rows, and the totals in columns by
month. Because the values are in a single control on the form, though, I
can’t figure out how to get a percentage calculated. The ones on the bottom
row, of course, had only one condition ("Valid"). I tried putting a column in
the xtab to calculate it but got a message that a xtab will only accept one
column.

And, this is the simplest of the calculations I’m facing. I have another
report that has 7 rows and the percentage has to be calculated by the current
month. So I was starting with the “simplest.�

Hope this clarifies. Thanks.

--
susan


"Maverick" wrote:

Susan,

Why are you trying to get two calculations in one field? You either want the
value for valid or value for invalid in one field. Your approach is trying to
say that if Transactions = Valid then give me the percentage of valid and if
Transactions Valid then if Transactions = Invalid then give me the