I have a report problem that I can't seem to get the anxwer on.

[dbar10]

I have a tricky problem. I have a report that contains information about amounts paid for service and they have a RANumber that's on each line with the amount. Then in the invoice header I am showing a text box with the total values for each of two RANumbers. I am getting the Sum of each of the two RANumbers just fine with:

RA1Sum :=Sum(IIf([NPI]<>1780694984,0,[TotalPaid]))
RA2Sum:=Sum(IIf([NPI]<>1649307471,0,[TotalPaid]))
The NPI field is a constant and unique to each RA

Now the problem: I have another textbox to act as a variable label for each sum. The RANumber has to show for the RA1 and Show the RANumber for RA2. I tried this:
RA1 Label: =IIf([NPI]<>1780694984,"",[RANum])
RA2 Label: =IIf([NPI]<>1649307471,"",[RANum])

This works fine if I only have values for one RANumber in the detail. But as soon as I have 2 then I get the Sum of the values for both, but I only get the label for the first RANumber, but the label for the second does not show up. I hope soomeone can understand what I am trying to do. I am stuck.

 

[dhookom]

Try something like:

RA1 Label: =Max(IIf([NPI]<>1780694984,Null,[RANum]))
RA2 Label: =Max(IIf([NPI]<>1649307471,Null,[RANum]))

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Thanks for your reply dhhokom. I tried your solution. However, when two different NPI's show in the detail, both dollar amounts are calculated as always but the First TextLabel RANum appears, good. The second returns a 0. I assume for "true" in the field. Any other ideas, please. Thanks

 

[dhookom]

Provide use with about 6 sample records with the desired display in the report.

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Ok Here is a sample with desired out come:
07954035 = $42.50
07954455 = $144.16

Date Service Amt Paid Recoup RA# NPI
11/11/08 T1019 $16.96 0.00 07954035 178
11/13/08 T1000 $12.72 0.00 07954035 178 11/14/08 T1019 $12.72 0.00 07954035 178
11/11/08 S5130 $50.88 0.00 07954455 164
11/13/08 S5150 $42.40 0.00 07954455 164
11/14/08 S5130 $50.88 0.00 07954455 164

I really appreciate your trying to help Doug

 

[dbar10]

sorry try again
Ok Here is a sample with desired out come:
07954035 = $42.50
07954455 = $144.16

Date Service Amt Paid Recoup RA# NPI
11/11/08 T1019 $16.96 0.00 07954035 178
11/13/08 T1000 $12.72 0.00 07954035 178
11/14/08 T1019 $12.72 0.00 07954035 178
11/11/08 S5130 $50.88 0.00 07954455 164
11/13/08 S5150 $42.40 0.00 07954455 164
11/14/08 S5130 $50.88 0.00 07954455 164

I really appreciate your trying to help Doug

 

[dhookom]

It worked for me once I figured your longer values like "1780694984" needed to be truncated to "178". I'm not sure what your actual data is since you seem to have either entered the wrong expressions or provided the wrong values in your sample.

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

I'm sorry, I had some old stuff sitting in some of the text fields. I've been banging on this for a while and went brain dead. Your answer works great, now that I cleaned up my act. Thank you very much. I appreciate this alot. Keep up the good work.