expression too complex error - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

expression too complex error

Sami Shamma

2 years ago

I have this strange situation, never encountered it before.
A report that has been running for a long time and still does when I call in with this code:

Details

Private Sub PrintBtn_Click()
    
    Dim SP As Boolean
    SP = DLookup("ShowPreview", "SettingT", "SettingID=1")
    If SP Then
        DoCmd.OpenReport "ChapR", acViewPreview, , "ChapID= " & ChapID
    Else
        DoCmd.OpenReport "ChapR", acViewNormal, , "ChapID= " & ChapID
    End If

End Sub

however I get the error shown below when I call it without any "WHERE" statement or with the following code:

Details

Private Sub PrintByChaplainBtn_Click()

    Dim SP As Boolean
   
    SP = DLookup("ShowPreview", "SettingT", "SettingID=1")
    If SP Then
        DoCmd.OpenReport "ChapR", acViewPreview, , "YearMonthID= " & YearMonthID
        
    Else
        DoCmd.OpenReport "ChapR", acViewNormal, , "YearMonthID= " & YearMonthID
    End If

End Sub

The Report Record Source is an updatable query that runs correctly.

The "ChapID" is the primary Key on the query and YearMonthID is a foreign key

	Sami Shamma OP @Reply 2 years ago

	Sami Shamma OP @Reply 2 years ago
I hit a wall with this one. The OnLoad event fires but the error message comes before OnOpen event. Help please guys

Adam Schwanz @Reply

2 years ago

To dumb down the problem more, does it do it with each individual statement? Like just
DoCmd.OpenReport "ChapR", acViewPreview, , "YearMonthID= " & YearMonthID all by itself?

The query that is the record source opens up fine on its own and shows all the data?

If you open the report from the navigation pane without any code does it still throw the error?

I'm leaning towards there's a calculation inside the report itself.

	John Campbell @Reply 2 years ago
I have had this happen to me as well. Usually on queries that have a ton of criteria. Your best bet is to break things up in pieces to try and figure it out. I would also hard code some criteria to test it.

	Sami Shamma OP @Reply 2 years ago
Thank you John My query is strait forward and works with in forms reports and even this report. based on one table with a lookup table. Adam. yes, It even fails when I try to run it from the object navigator. yet runs just fine with the first code.

	Adam Schwanz @Reply 2 years ago
Well that is strange, if it's not confidential Sami you can email it to me and I'll take a look. Otherwise spam upload pictures of everything ;)

	Sami Shamma OP @Reply 2 years ago
Thank you, Adam. Unfortunately, the data is state property and confidential. I will see if I can create a test backend. I am heading to the UK tomorrow, so I may not be able to do this until I return in 10 days.

	Adam Schwanz @Reply 2 years ago
No problem, have fun, visiting Alex? lol

	Sami Shamma OP @Reply 2 years ago
I tried to visit him but the schedules did not work out. Will all meet in Florida I hope soon

	David Burns @Reply 2 years ago
Don't forget your umbrella!

	Sami Shamma OP @Reply 2 years ago
Thank you David for the reminder Not a stranger to UK weather, lived there for 20 years.

	Sami Shamma OP @Reply 2 years ago
Ok, sitting in the lounge at BDL (Hartford) Airport with time to kill. It turned out that the report was corrupted. I created a new report and moved the controls one section at a time, and the darn thing is working. At least my sanity is confirmed for a little longer.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View