How to debug a report

[WCStarks]

I have a report which has broken. The supporting queries run just fine, but the report, which used to work, now produces and error. I can't find code for the report to enter a breakpoing in. How can I walk through a report in debug mode?

[pbaldy]

What's the error message? If it's a parameter prompt, you've got something referencing a deleted field or something Access can't find.

[WCStarks]

The error is:
Run-time error" 3071; This expression is typed incorrectly, or it is too complex to be evaluated. For example ....

[WCStarks]

not relevant

[orange]

Can you post a copy of the database with instructions to highlight the issue? Or the offending expression?
Hard to offer more than Paul has already without seeing the database.

[pbaldy]

Hmm, the query runs okay? Have you gone to the last record in the query? Sometimes the error is in a particular record and isn't thrown unless that record is accessed.

[WCStarks]

The Stars_Awards report calls Stars_Awards query, which calls Star_AW query. This purpose of this report is to show when each member got their last service star(s) pin and when their next one is due. I can run the Star_AW alone just fine. I can run the Stars_Awards query just fine. But when I run the Report, I get the indicated 3071 error. The report has one expression

=IIf([Yrs]<5,DateAdd("yyyy",5,[Join]),[AwDate])

I tested running the report with this code removed, but the error still occurs.

Star_AW query calls ElookUp() three times to get the most recent Membership status for "Award".

Here is the sanitized version of the db:

SCATeam.zip

[pbaldy]

AwDate throws an error for the last record in the query, which is likely the source of your problem. That guest record has no join date.

[pbaldy]

As a test, I added

<>"Guest"

as a criteria on the "Call" field in Star_AW to exclude that record and the report opens fine (entered 2018 for year).

[WCStarks]

In the sanitized db I sent you, I forgot to leave "Guest" in the Mem_Name field. I did leave the call sign as is. So, Star_AW should have excluded the guest record by the criteria on Mem_Name. It would not have worked in the sanitized version, however. So you filtering on Call Sign should not have been necessary. Please try it again with "Guest" in the Mem_Name field and see what happens. I added the filter on the Call field, but as expected, the error still occurs. When I look at the result set, No Guest is listed.

[pbaldy]

I went to that record and put Guest in the mem_name field and took out the criteria I added. That record does get excluded by your existing criteria and the report opens without error. Do you have any records in your actual db that return #Error in any of the query fields? The guest record caused an error because it had no join date.

[pbaldy]

By the way, if I leave the guest record in and returning #Error in the query, the report throws the error you mention. That leads me to believe that it's the error in the query, not something in the report, that's the source of the problem.

[WCStarks]

The [Joined] field in the record is not used is this query. That field, as is the Resigned field, is populated from Membership table via ELookup() functions. The only Member information retrieved from the Members record are the Mem_Name, Call and 1J numbers. The Guest record should not be an issue, as it is excluded from the result set. All the member status information is retrieved directly from the Membership table, with 3 ELookUp() function calls, When I run the queries, I get no error. But when I run the report, I get the error.

[pbaldy]

I'm not asking if you get an error message, I'm asking if you see #Error anywhere in the result set. In the sample, the query ran without error, but displayed #Error in that field. The report can't handle that so throws up the error message.

[WCStarks]

Thank you. Yes, I see it. It was retrieving a special blank record used to indicate an Event staffing vacancy. That record has ID 51. It was not being excluded. It is also an "Active" record and would be retrieved with the others. I am not sure how that criteria got lost, but I put it in again "ID <> 51", and now the report works. I guess I was overlooking the #error in that last record in the list, subconsciously associating it with the new record at the bottom of any continuous form or table. Very stupid of me. Sorry for the trouble. I am still perplexed at how that criteria got lost, as I don't recall messing with this report since it was run last, but I must have.