Solved OpenReport and Criteria (1 Viewer)

[Larkin]

Hello all,

I am trying to export a filtered report on button click. For some reason the filter does not work, and it exports a blank report. Please find the code below. Any help would be appreciated.

DoCmd.OpenReport ReportName, acViewPreview, , "[Assignment Details].[Assignment ID]=" & TempVars!DataPass, acHidden

 

[theDBguy]

What do you see if you don't hide the report? Are you sure the TempVars has the correct data? What does your export code look like?

 

[Larkin]

If I do not hide the report it just pops up the same blank report. I am positive the tempvar is functional because if I change the where criteria to "Assignment ID=" & TempVars!DataPass I can see the proper data in the missing operator error box. See the full report export code below

DoCmd.OpenReport ReportName, acViewPreview, , "[Assignment Details].[Assignment ID]=" & TempVars!DataPass, acHidden
DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FileName
DoCmd.Close acReport, ReportName, acSaveNo

And here are the variable arguments Just in case

ReportName = "Order Form"
FileName = CurrentProject.Path & "\REDACTED Order Form.PDF"

 

[theDBguy]

If I do not hide the report it just pops up the same blank report. I am positive the tempvar is functional because if I change the where criteria to "Assignment ID=" & TempVars!DataPass I can see the proper data in the missing operator error box. See the full report export code below

DoCmd.OpenReport ReportName, acViewPreview, , "[Assignment Details].[Assignment ID]=" & TempVars!DataPass, acHidden
DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FileName
DoCmd.Close acReport, ReportName, acSaveNo

And here are the variable arguments Just in case

ReportName = "Order Form"
FileName = CurrentProject.Path & "\REDACTED Order Form.PDF"

Hmm, if changing the criteria to simply say Assignment ID and it works, why then do you use Assignment Details in the non-working version? Is that still needed?

 

[Larkin]

So if I change the code to:

DoCmd.OpenReport ReportName, acViewPreview, , "Assignment ID=" & TempVars!DataPass, acHidden

Then I get a missing operator syntax error. I (possibly incorrectly) assumed that the issue was it was not properly referencing the field, so I coded it to reference the field and now it does not filter but does generate the report.

Possibly of interest, the report is based on a query that uses the same criteria to filter the query, and that works independently fine, however when using the DoCmd.OpenReport it apparently ignores the criteria in the query.

 

[theDBguy]

So if I change the code to:

DoCmd.OpenReport ReportName, acViewPreview, , "Assignment ID=" & TempVars!DataPass, acHidden

Then I get a missing operator syntax error. I (possibly incorrectly) assumed that the issue was it was not properly referencing the field, so I coded it to reference the field and now it does not filter but does generate the report.

Possibly of interest, the report is based on a query that uses the same criteria to filter the query, and that works independently fine, however when using the DoCmd.OpenReport it apparently ignores the criteria in the query.

This may require deeper troubleshooting steps. Are you able to post a sample db with test data?

 

[pbaldy]

Assignment ID needs to be bracketed because of the inadvisable space.

 

[Larkin]

@theDBguy I would honestly have to make a completely fake DB as even the field names and calculations I can not distribute as they contain information that is covered by an NDA.

@pbaldy I attempted that, and just to be sure I did this correctly the code is below, but it still generates an empty report, though it does not give a syntax error

DoCmd.OpenReport ReportName, acViewPreview, , "[Assignment ID]=" & TempVars!DataPass, acHidden
DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FileName
DoCmd.Close acReport, ReportName, acSaveNo

 

[Larkin]

Do you think coding in running the query before opening the report might resolve this?

 

[theDBguy]

Do you think coding in running the query before opening the report might resolve this?

I'd say give it a shot...

 

[Larkin]

@theDBguy Turns out that was a great call, doing this allowed me to find that the reason the reports were blank had nothing to do with the code, apparently in my test cases I was leaving one specific field blank that was effectively causing the query to return no records. Upon discovering this I redid the joins in the query SQL and it has been resolved. Important lesson for anyone having a similar issue, make sure when testing something like this that you methodically evaluate each field for NULL vs Not NULL and see if that causes an issue like this. Better testing procedure on my end would have alleviated this issue.

 

[theDBguy]

@theDBguy Turns out that was a great call, doing this allowed me to find that the reason the reports were blank had nothing to do with the code, apparently in my test cases I was leaving one specific field blank that was effectively causing the query to return no records. Upon discovering this I redid the joins in the query SQL and it has been resolved. Important lesson for anyone having a similar issue, make sure when testing something like this that you methodically evaluate each field for NULL vs Not NULL and see if that causes an issue like this. Better testing procedure on my end would have alleviated this issue.

Congratulations! Glad to hear you got it sorted out. Cheers!