vb error 3071

Question

Hello Everyone,

All of the sudden I am getting VB Error 3071 when running a report, it has been running fine for about a year. All other reports launching from the form work fine this one form is giving me an issue. The area in bold is causing the error. any suggestions

The festival year is a unbound text field located in the frmReporting

rptVendorSpaceAssignments pulls from qryVendorSpaceInfo

Option Compare Database

Private Sub Form_Close()

DoCmd.ShowToolbar "Ribbon", acToolbarYes

DoCmd.OpenForm "frmStart", acNormal

End Sub

Private Sub Form_Open(Cancel As Integer)

DoCmd.ShowToolbar "Ribbon", acToolbarNo

End Sub

Private Sub OpenTotalsReport_Click()

If IsNull(Me.txtFestivalYear) Then

Me.txtFestivalYear.SetFocus

MsgBox "Enter Festival Year", vbExclamation

Exit Sub

End If

DoCmd.OpenReport "rptTotals", acViewPreview

End Sub

Private Sub rptBalanceDue_Click()

If IsNull(Me.txtFestivalYear) Then

Me.txtFestivalYear.SetFocus

MsgBox "Enter Festival Year", vbExclamation

Exit Sub

End If

DoCmd.OpenReport "rptBalanceDue", acViewPreview

End Sub

Private Sub rptVendorSpaceCheckin_Click()

If IsNull(Me.txtFestivalYear) Then

Me.txtFestivalYear.SetFocus

MsgBox "Enter Festival Year", vbExclamation

Exit Sub

End If

DoCmd.OpenReport "rptVendorCheckInStatus", acViewPreview

End Sub

Private Sub RunTaxIDRpt_Click()

If IsNull(Me.txtFestivalYear) Then

Me.txtFestivalYear.SetFocus

MsgBox "Enter Festival Year", vbExclamation

Exit Sub

End If

DoCmd.OpenReport "rptVendorTaxIDInfo", acViewPreview

End Sub

Private Sub runVendorSpaceAssignments_Click()

If IsNull(Me.txtFestivalYear) Then

Me.txtFestivalYear.SetFocus

MsgBox "Enter Festival Year", vbExclamation

Exit Sub

End If

DoCmd.OpenReport "rptVendorSpaceAssignments", acViewPreview

End Sub

This is the sql for the qryVendorSpaceInfo

SELECT tblVendorInfo.FirstName, tblVendorInfo.LastName, tblVendorInfo.BusinessName, tblVendorInfo.VendorType, tblVendorInfo.DescProdService, tblPaymentInfo.SpaceNumber1, tblPaymentInfo.SpaceNumber2, tblPaymentInfo.SpaceNumber3, tblPaymentInfo.FestivalYear, tblVendorInfo.PhoneNumber, tblVendorInfo.CellNumber, tblPaymentInfo.SpaceNumber4

FROM tblVendorInfo INNER JOIN tblPaymentInfo ON tblVendorInfo.VendorID = tblPaymentInfo.VendorID

WHERE (((tblPaymentInfo.FestivalYear)=[Forms]![frmReporting]![txtFestivalYear]))

ORDER BY tblPaymentInfo.SpaceNumber1;

Answer 1

So removing the CLng function would have worked. You cannot convert Null to Long.

One of the bigger pictures is that your database design has a "repeating group" with the four Space slots. This violates proper database design principles (e.g. http://en.wikipedia.org/wiki/First_normal_form)

If you spun them off in their own table, they would be required so null could no longer happen.

Answer 2

I'm grasping at straws. Try removing the CLng function from the sql of the report.

Also in qryVendorSpaceInfo temporarily remove the where-clause, or move it to the sql of the report.

Did you check if you have missing references?

Did you compact the database?

Answer 3

this is the sql of the report

SELECT qryVendorSpaceInfo.FirstName, qryVendorSpaceInfo.LastName, qryVendorSpaceInfo.BusinessName, qryVendorSpaceInfo.VendorType, qryVendorSpaceInfo.DescProdService, qryVendorSpaceInfo.SpaceNumber1, qryVendorSpaceInfo.SpaceNumber2, qryVendorSpaceInfo.SpaceNumber3, qryVendorSpaceInfo.FestivalYear, qryVendorSpaceInfo.PhoneNumber, qryVendorSpaceInfo.CellNumber, CLng([SpaceNumber1]) AS SpcNum1, CLng([SpaceNumber2]) AS SpcNum2, CLng([SpaceNumber3]) AS SpcNum3, qryVendorSpaceInfo.SpaceNumber4, CLng([SpaceNumber4]) AS SpcNum4

FROM qryVendorSpaceInfo;

Answer 4

Yes I am getting the too complex error. The exact text is

Error: 3071

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

as far as coding it is just pulling from the query I listed above.

Answer 5

This may have nothing to do with it, but I noticed you don't have OPTION EXPLICIT in your module. This should be in ALL modules.

I found that error 3071 is "too complex" error, is that what you are getting? Can you quote the error message verbatim, please?

If indeed too complex, I would say the query is not too complex. Is there any code-behind in the report?