RE: Calculations in Reports

Tech-Archive recommends: Fix windows errors by optimizing your registry

You say combining the statements will not work?
Now I agree I've been trying! Any suggestions??


"Dan @BCBS" wrote:

I appreciate the help - I'd really like to conclude this and I'm still trying
to figure out a way..


"Klatuu" wrote:

No, that is not going to work. I'm still thinking about it.
--
Dave Hargis, Microsoft Access MVP


"Dan @BCBS" wrote:

What about merging these two statements statements???

=Count(IIf(Month([IssueCloseDate])=1,1,Null))
=IIf([Days]<30 And [SourceType]="M" And [InsuranceType]='HO',Sum([Jan]),0)

Something like this (but this don't work).

=Count(IIf(Month([IssueCloseDate])=1,1,Null, Sum ([LessThen30Days]<30 And
[SourceType]="M" And [InsuranceType]='HO'))



"Klatuu" wrote:

I'm not sure what the problem is, Dan. It may be because [jan] is embedded
in the formula that it is getting confused. I don't have a suggestion at the
moment. I will think about it and see if I can come up with a solution.
--
Dave Hargis, Microsoft Access MVP


"Dan @BCBS" wrote:

Getting help when I get stuck thru this group has probably saved my job more
times than I could count. I don't know how you guys keep the questions
straight!!

But to the issue:
With the code you suggest I get the pop up "Enter Parameter Value" so it's
not recognizing the Jan Value from the first Text Box???

Suggestions??


"Klatuu" wrote:

Dan,
I'm not absolutely sure the modification for 2) below will work and I don't
have a way to test it. See if this does the trick:


2. Text Box "B" =IIf([Days] < 30 and [SourceType]="M" And
[InsuranceType]='HO', Sum([Jan]), 0)

If it does, the same change should work for 3 and 4.

Let me know if that works out.

Sorry about the confusion, but then it doesn't take much to confuse me :)
--
Dave Hargis, Microsoft Access MVP


"Dan @BCBS" wrote:

PLEASE DISREGARD PREVIOUS MESSAGE

I had left some comments at the end, that I wanted to delete.
All I wanted to say was this:

I cannot use what worked - that is why I asking for help!
Guess that's were I confused you...

My query calculated the number of cases per month: Jan:
Count(IIf(Month([IssueCloseDate])=1,1,Null))
Which I now have to code in the report instead of the query.

Let me asked my question like this: "In 2-3-4 below I need to use Jan in the
calculation"

This is what I need to do: ( 1. works fine)

1. Text Box "Jan" =Count(IIf(Month([IssueCloseDate])=1,1,Null))

2. Text Box "B" =Sum(IIf([Days] < 30 and [SourceType]="M" And
[InsuranceType]='HO' ,[Jan],0))

3. Text Box "C" =Sum(IIf([Days] < 30 and [SourceType] In ('m','i') And
[InsuranceType]='HO',[Jan],0))

4. Text Box "D" =Sum ( [Jan] - [Text Box "C"]
5. Text Box "E" =Sum ( [Text Box "C"] / [Text Box "D"])

Please help.
Dan


"Dan @BCBS" wrote:

I cannot use what worked - that is why I asking for help!
Guess that's were I confused you...

My query calculated the number of cases per month: Jan:
Count(IIf(Month([IssueCloseDate])=1,1,Null))
Which I now have to code in the report instead of the query.

Let me asked my question like this: "In 2-3-4 below I need to use Jan in the
calculation"

This is what I need to do: ( 1. works fine)

1. Text Box "Jan" =Count(IIf(Month([IssueCloseDate])=1,1,Null))

2. Text Box "B" =Sum(IIf([Days] < 30 and [SourceType]="M" And
[InsuranceType]='HO' ,[Jan],0))

3. Text Box "C" =Sum(IIf([Days] < 30 and [SourceType] In ('m','i') And
[InsuranceType]='HO',[Jan],0))

4. Text Box "D" =Sum ( [Jan] - [Text Box "C"]
5. Text Box "E" =Sum ( [Text Box "C"] / [Text Box "D"])

Please help.
Dan



Text Box B. =Count(IIf(Month([IssueCloseDate])=1,1,Null) And
([InsuranceType]='HO')) ("This Don't Work")
Text Box C. =Count(IIf(Month([IssueCloseDate])=1,1,Null))



My original message said my query will not allow me to create another
calculation so I need to do all the calculations in the report.

So, let me ask my question like this:

I have this code in my report which counts the cases in Jan:
Name: Jan
Control Source: =Count(IIf(Month([IssueCloseDate])=1,1,Null))

I need to use the results of Jan in another calculation (Text Box) like this
but this don't work:
Name: Results
Control Source: =Sum(IIf([SourceType]="M" And [InsuranceType]='HO',[Jan],0))

You suggested: =IIf([SourceType]="M" And [InsuranceType]='HO',Sum([Jan]),0)
Which gives me a pop up "Enter Parameter Value"


Bottom line: I have to perform more calculations using Jan - but I need to
get one first.
The others are:
1. =Sum(IIf([SourceType]="M" And [InsuranceType]='HO',[Jan],0))
2. =Sum(IIf([SourceType]="i" And [InsuranceType]='HO',[Jan],0))
3. =Sum(IIf([SourceType] In ('m','i') And [InsuranceType]='HO',[Jan],0))
4.



"Klatuu" wrote:

That's not what I suggested. My suggestion was what you said worked:
Text Box 2: =IIf([SourceType]="M" And [InsuranceType]='HO',Sum([Jan]),0)


--
Dave Hargis, Microsoft Access MVP


"Dan @BCBS" wrote:

Any idea why "Jan" is not recognize and the pop up appears saying ' Enter
PArameter Value"????????

Thanks


"Dan @BCBS" wrote:

In your example below you had me add "Sum" before [Jan]
This creates a pop up window asking for the value of Jan.....

Text Box "Jan"
=Count(IIf(Month([IssueCloseDate])=1,1,Null))


Text Box "Results"
=IIf([SourceType]="M" And [InsuranceType]='HO',Sum([Jan]),0)

Suggestions - Thanks





"Klatuu" wrote:

Try it this way:
Text Box 2: =IIf([SourceType]="M" And [InsuranceType]='HO',Sum([Jan]),0)

--
Dave Hargis, Microsoft Access MVP

"Dan @BCBS" wrote:

I was asked to add something to a report. The query which generates the
report does not allow the action.

So I'm trying to perform the actions in the report instead of limiting the
data in the query.

Text Box 1: (Named "Jan") =Count(IIf(Month([IssueCloseDate])=1,1,Null))
Text Box 2: =Sum(IIf([SourceType]="M" And [InsuranceType]='HO',[Jan],0))

Text box 2 don't work - it's not recognizing Jan (Text Box 1)

Suggestions?????
.

Relevant Pages