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]

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.

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]

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.

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))