Printing records in a report only if value is positive 1

[Gavroche00]

I have a report where if the value of salary is >0, I want it printed on check paper, and if value = 0 I want it printed on regular paper. I have 70 records in the report, with one page per record. Where do I specify the batch request and can I request both at once?

Thank you

 

[scriverb]

Create a query with a criteria that filters that selects record where the salary is greater than 0. These would be on the check stock. Create another query that selects the records to be printed only on the report paper. Save them as two seperate queries. Load the queries to the report in the reports OnOpen event procedure depending on which one you want to print.

Post back if you need help coding this process.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

Another way to do this is to prompt for which printing option the user wants to print and setup a filter for the report. This would be done on a form and allow for a single query without a criteria included to be used for the report. The prompting in the forms command button for the report printing would trigger different criteria selection through the report filter parameter.

Post back if you need help setting this up.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Gavroche00]

Bob,

The calculations are done in the report itself and not in the query. Are you saying to add a field in the query that calculates a total and then create an option depending if the result of that box is >0?

Thank you

David

 

[scriverb]

Yes. It is always better to perform these calculations in the query and then use the results for your report. A calculated field with a criteria expression to select either option would work well in this situation.

Post back with the specifics of your query and I can help you with this.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Gavroche00]

Bob,

My calculated field is of the sort total[a]++[c]). I understand a criteria such as total>0 but how do I enter a criteria with both options? I need two printouts, one for positive and one for negative values. If I write total >0 on the first line and total<0 on the second, I believe everything will get printed. Thank you for the help.

David

 

[scriverb]

Before we continue do you want the report to pause and ask the question as to which paper stock and then prompt the user to make sure that the correct paper stock is in the printer before continuing?

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Gavroche00]

Absolutely, that would be a great idea.

David

 

[scriverb]

Put the following code in the Reports Open Event Procedure:

Dim x as variant
x = MsgBox("Print on Check Stock??", vbYesNo, "Printing Stock Prompt")
If x = vbYes then
   Me.RecordSource = "[i][red]Select SQL for Check stock selection[/red][/i]"
else
   Me.RecordSource = "[i][red]Select SQL for plain paper selection[/red][/i]"
End if
x = MsgBox("Ready to print?", vbOKOnly, "Printing Authorization Prompt")

The code to load up the Me.RecordSource above can be amended to identify Saved queries that are already to print depending upon the value of the Total column.

Post back with any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Gavroche00]

Bob,

If I save two queries depending on the criteria in the calculated field, I just need to call it then? Me.recordsource = "qry_greaterthenzero" for example?

Thank you

David

 

[scriverb]

Yes, that is correct. Just use the code above to determine which to install.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]