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

[gonella123]

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

SUM Funtion : =Sum(IIf([lob1] Like "WC*",[premium1],
                   IIf([lob2] Like "WC*",[premium2],
                   IIf([lob3] Like "WC*",[premium3],
                   IIf([lob4] Like "WC*",[premium4],
                   IIf([lob5] Like "WC*",[premium5],
                   IIf([lob6] Like "WC*",[premium6],
                   IIf([lob1] Like "PWC*",[premium1],
                   IIf([lob2] Like "PWC*",[premium2],
                   IIf([lob3] Like "PWC*",[premium3],
                   IIf([lob4] Like "PWC*",[premium4],
                   IIf([lob5] Like "PWC*",[premium5],
                   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.

[NeoPa]

You may just get away with :

SUM Funtion : =Sum(IIf(([lob1] Like "WC*") Or ([lob1] Like "PWC*"),[premium1],
                   IIf(([lob2] Like "WC*") Or ([lob2] Like "PWC*"),[premium2],
                   IIf(([lob3] Like "WC*") Or ([lob3] Like "PWC*"),[premium3],
                   IIf(([lob4] Like "WC*") Or ([lob4] Like "PWC*"),[premium4],
                   IIf(([lob5] Like "WC*") Or ([lob5] Like "PWC*"),[premium5],
                   IIf(([lob6] Like "WC*") Or ([lob6] Like "PWC*"),[premium6],0)))))))

[NeoPa]

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.

[gonella123]

@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.

[gonella123]

@gonella123
is there any way by using functions... we can do in a better way...
please include steps...

[NeoPa]

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.

Public Function ChoosePremium(ByRef strLob1 As String, _
                              ByRef strLob2 As String, _
                              ByRef strLob3 As String, _
                              ByRef strLob4 As String, _
                              ByRef strLob5 As String, _
                              ByRef strLob6 As String) As Integer
    Dim strWork As String
    Dim intPos As Integer
 
    strWork = Chr(0) & strLob1 & _
              Chr(0) & strLob2 & _
              Chr(0) & strLob3 & _
              Chr(0) & strLob4 & _
              Chr(0) & strLob5 & _
              Chr(0) & strLob6
    intPos = InStr(1, strWork, Chr(0) & "WC", vbBinaryCompare)
    If intPos = 0 Then _
        intPos = InStr(1, strWork, Chr(0) & "PWC", vbBinaryCompare)
    Select Case intPos
    Case 1
        ChoosePremium = 1
    Case Len(strLob1) + 2
        ChoosePremium = 2
    Case Len(strLob1) + _
         Len(strLob2) + 3
        ChoosePremium = 3
    Case Len(strLob1) + _
         Len(strLob2) + _
         Len(strLob3) + 4
        ChoosePremium = 4
    Case Len(strLob1) + _
         Len(strLob2) + _
         Len(strLob3) + _
         Len(strLob4) + 5
        ChoosePremium = 5
    Case > 0
        ChoosePremium = 6
    Case Else
        ChoosePremium = 7
End Function

You know how to use Choose() to convert this value to a selection from your list?

[NeoPa]

The usage would be something like :

SUM Funtion : =Sum(Choose(ChoosePremium([Lob1],
                                        [Lob2],
                                        [Lob3],
                                        [Lob4],
                                        [Lob5],
                                        [Lob6]),[Premium1],
                                                [Premium2],
                                                [Premium3],
                                                [Premium4],
                                                [Premium5],
                                                [Premium6],
                                                0))