My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Strange result in bonus count
|
|
 Feb 17 2006, 08:03 AM
Post
#1
|
|
|
UtterAccess Addict Posts: 221  |
Hi there,
I have a report that lists all the sold policies per salesperson_ID. The report counts all sold policies for each salesperson_ID. From that number the projected bonus is derived using: CODE Private Sub GroupHeader1_Print(Cancel As Integer, PrintCount As Integer) Select Case Policy Case 1 To 4 Bonus = 0 Case 5 To 9 Bonus = 100 Case 10 To 14 Bonus = 200 Case 15 To 19 Bonus = 300 Case 20 To 999 Bonus = 500 End Select End Sub This workes fine. But when there is a cancellation, this happens without a salesperson_ID attached, the report shows 0 as the salesperson_ID, which is correct as there is no salesperson. The strange thing is, and this happens not within every shopheader, the bonus returns a value of 100, with no policies sold. If there is no salesperson_ID shown, it should not count anything, no policies, no bonus. How can I define in the above standing code that if there is no Salesperson_ID, Bonus is automatically 0.
Attached File(s)
|
 |
 
|
|
Feb 17 2006, 09:12 AM
Post
#2
|
|
|
UtterAccess VIP / UA Clown Posts: 25,026 From: LI, NY |
CODE Private Sub GroupHeader1_Print(Cancel As Integer, PrintCount As Integer) If IsNull(Salesperson_ID) Then Bonus = 0 Else Select Case Policy Case 1 To 4 Bonus = 0 Case 5 To 9 Bonus = 100 Case 10 To 14 Bonus = 200 Case 15 To 19 Bonus = 300 Case 20 To 999 Bonus = 500 End Select End If End Sub That should do it. |
|
|
|
|
Feb 17 2006, 09:22 AM
Post
#3
|
|
|
UA Editor + Utterly Certified Posts: 22,722 From: Melton Mowbray,Leicestershire (U.K) |
Hi
I would think you need to evaluate the salesperson_ID and if valid/Is Not Null continue through the Select Case.. otherwise Sset bonus (0). (IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif) |
|
|
|
|
Feb 20 2006, 02:42 AM
Post
#4
|
|
|
UtterAccess Addict Posts: 221 |
Hi Scott and Larry,
I tried that code, didn't affect the bonus value. The references that I used are correct. It works though for the first line in the report, after that, I continues in it's 'wrong behaviour'. Larry, Im not sure what you mean. Sset Bonus (0), where do I put that in the VBA? Thanks |
|
|
|
|
Feb 20 2006, 03:39 AM
Post
#5
|
|
|
UtterAccess VIP Posts: 20,187 From: Colorado |
one of the problems may be that Policy is a calculated field on the report... when there are no records, perhaps it is not refreshing its value or something ...
Scott and Larry, some background... The report is grouped by ShopID and then Salesperson_ID. The policy calculation is in the Salesperson_ID_header (GroupHeader1) =Abs(Sum([ActivePolicy])) where ActivePolicy is a yes/no control in the detail My opinion is that it would be better to make a query to do some of the calculations instead of using so many calculated controls on the report |
|
|
|
|
Feb 21 2006, 06:52 AM
Post
#6
|
|
|
UtterAccess Addict Posts: 221 |
Hi all,
Created the query, but I strugled to get this integrated in the reports. But I found another solution. In the code I changed CODE Select Case Policy Case 1 To 4 Bonus = 0 Into: CODE Select Case Policy Case 0 To 4 Bonus = 0 Easier solution for a 3 pager report. Thanks for the input. |
|
|
|
|
Feb 21 2006, 09:12 AM
Post
#7
|
|
|
UtterAccess VIP Posts: 20,187 From: Colorado |
you're welcome, Jan (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)
You may want to rearrange the case statements... just to catch everything... CODE Select Case Policy
Case >= 20 Bonus = 500 Case >=15 Bonus = 300 Case >=10 Bonus = 200 Case >=5 Bonus = 100 Case else Bonus = 0 End Select |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 20th May 2013 - 08:20 AM |