By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 983 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Getting Error(#Error) in Report ... after converting Access2003 to 2007

P: 3
I am getting #Error while generating report, after converting the database from 2003 to 2007.

The "#Error" when run the report. On this you have report elements use nested if-statements as parameter to the sum() function. It seems like this works on 2003, but not on 2007.

Steps;
1) Open the zip file, rename the file, by remove _ at the end of the file.

2) If you run the report based on image02, you will get
#Error in the report.

3) this is becoz of the
Expand|Select|Wrap|Line Numbers
  1. SUM Funtion : =Sum(IIf([lob1] Like "WC*",[premium1],
  2.                    IIf([lob2] Like "WC*",[premium2],
  3.                    IIf([lob3] Like "WC*",[premium3],
  4.                    IIf([lob4] Like "WC*",[premium4],
  5.                    IIf([lob5] Like "WC*",[premium5],
  6.                    IIf([lob6] Like "WC*",[premium6],
  7.                    IIf([lob1] Like "PWC*",[premium1],
  8.                    IIf([lob2] Like "PWC*",[premium2],
  9.                    IIf([lob3] Like "PWC*",[premium3],
  10.                    IIf([lob4] Like "PWC*",[premium4],
  11.                    IIf([lob5] Like "PWC*",[premium5],
  12.                    IIf([lob6] Like "PWC*",[premium6],0)))))))))))))
From my understanding... there are more arguments passed, if 8 args, then report is generating.


Is there any way by writing VB functions.. to remove this error...

Please suggest.

Thanks,
Eswar.
Attached Images
File Type: jpg image001.jpg (7.2 KB, 163 views)
File Type: jpg image002.jpg (12.8 KB, 177 views)
File Type: jpg image003.jpg (12.3 KB, 181 views)
Attached Files
File Type: zip IIFStatement.zip (75.0 KB, 78 views)
Jun 21 '10 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,310
You may just get away with :
Expand|Select|Wrap|Line Numbers
  1. SUM Funtion : =Sum(IIf(([lob1] Like "WC*") Or ([lob1] Like "PWC*"),[premium1],
  2.                    IIf(([lob2] Like "WC*") Or ([lob2] Like "PWC*"),[premium2],
  3.                    IIf(([lob3] Like "WC*") Or ([lob3] Like "PWC*"),[premium3],
  4.                    IIf(([lob4] Like "WC*") Or ([lob4] Like "PWC*"),[premium4],
  5.                    IIf(([lob5] Like "WC*") Or ([lob5] Like "PWC*"),[premium5],
  6.                    IIf(([lob6] Like "WC*") Or ([lob6] Like "PWC*"),[premium6],0)))))))
Jun 21 '10 #2

NeoPa
Expert Mod 15k+
P: 31,310
There are a few different options with string comparisons (See ANSI Standards in String Comparisons), but I can see none that fit your particular situation. Had it been "PWC*" or "QWC*" then we could have used "[PQ]WC*", but we cannot tell it to look for nothing in the first character - essentially moving the string along one. As you're using Like as well, as opposed to =, we can't use an In() trick either.

A function could be written, but it would need twelve (12) parameters passed, which is clumsier than I like.
Jun 21 '10 #3

P: 3
@gonella123
still getting the same error even i used OR in SUM as you mentioned. Can you attach if you get any Numerical value if you use the query which you mentioned.
Jun 21 '10 #4

P: 3
@gonella123
is there any way by using functions... we can do in a better way...
please include steps...
Jun 21 '10 #5

NeoPa
Expert Mod 15k+
P: 31,310
The following function procedure should return the value related to which premium you'd like to use (1 to 6) and 7 means to use 0.
Expand|Select|Wrap|Line Numbers
  1. Public Function ChoosePremium(ByRef strLob1 As String, _
  2.                               ByRef strLob2 As String, _
  3.                               ByRef strLob3 As String, _
  4.                               ByRef strLob4 As String, _
  5.                               ByRef strLob5 As String, _
  6.                               ByRef strLob6 As String) As Integer
  7.     Dim strWork As String
  8.     Dim intPos As Integer
  9.  
  10.     strWork = Chr(0) & strLob1 & _
  11.               Chr(0) & strLob2 & _
  12.               Chr(0) & strLob3 & _
  13.               Chr(0) & strLob4 & _
  14.               Chr(0) & strLob5 & _
  15.               Chr(0) & strLob6
  16.     intPos = InStr(1, strWork, Chr(0) & "WC", vbBinaryCompare)
  17.     If intPos = 0 Then _
  18.         intPos = InStr(1, strWork, Chr(0) & "PWC", vbBinaryCompare)
  19.     Select Case intPos
  20.     Case 1
  21.         ChoosePremium = 1
  22.     Case Len(strLob1) + 2
  23.         ChoosePremium = 2
  24.     Case Len(strLob1) + _
  25.          Len(strLob2) + 3
  26.         ChoosePremium = 3
  27.     Case Len(strLob1) + _
  28.          Len(strLob2) + _
  29.          Len(strLob3) + 4
  30.         ChoosePremium = 4
  31.     Case Len(strLob1) + _
  32.          Len(strLob2) + _
  33.          Len(strLob3) + _
  34.          Len(strLob4) + 5
  35.         ChoosePremium = 5
  36.     Case > 0
  37.         ChoosePremium = 6
  38.     Case Else
  39.         ChoosePremium = 7
  40. End Function
You know how to use Choose() to convert this value to a selection from your list?
Jun 22 '10 #6

NeoPa
Expert Mod 15k+
P: 31,310
The usage would be something like :
Expand|Select|Wrap|Line Numbers
  1. SUM Funtion : =Sum(Choose(ChoosePremium([Lob1],
  2.                                         [Lob2],
  3.                                         [Lob3],
  4.                                         [Lob4],
  5.                                         [Lob5],
  6.                                         [Lob6]),[Premium1],
  7.                                                 [Premium2],
  8.                                                 [Premium3],
  9.                                                 [Premium4],
  10.                                                 [Premium5],
  11.                                                 [Premium6],
  12.                                                 0))
Jun 22 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.