Base report on different queries

[NeilT123]

I currently have a report rptNTotalsByField which is based on a query qryrptNTotalsAndSNS. Unfortunately a change in legislation means that from now on I need to use a new set of data tables but still need to be able to reproduce the reports with the old data.

I have a trigger “CroppingYear” which is in a table tblSLTRpt and what I would like is:

when CroppingYear is 2011 or later then base rptNTotalsByField on qryrptNTotalsAndSNS2011 if not use the existing query.

CroppingYear is a number not a date.

I have not done this before so help with the coding and also where I store the coding would be appreciated.

Thanks in advance

 

[sxschech]

Is CroppingYear 2011 determined by selecting the year on a form or in the query itself?

 

[NeilT123]

OK, both!

I have a report selection form and on that form is an unbound combo which is croppingyear so in the 1st instance you can select the croppingyear(s) that you want the report to be based on. This changes the value in tblSLTRpt.

Then when the report is run, from a button on the form, CroppingYear is also bound into the query that the report is based on.

 

[sxschech]

An easy way would be to add code to your ON CLICK event of the button that runs the report.

You could do something like below:

If Me.Combo0 >= 2011 Then
    DoCmd.CopyObject , "qryrptNTotalsAndSNSYears", acQuery, "qryrptNTotalsAndSNS2011"
Else
    DoCmd.CopyObject , "qryrptNTotalsAndSNSYears", acQuery, "qryrptNTotalsAndSNS2010" 
End If

in the above code, change the name of Me.Combo0 to the actual name of your combo box.

If your year is text and not a number, change 2011 to "2011"

Change the name of your queries in the part after acQuery to what your actual source query names are

Change the record source of your report to qryrptNTotalsAndSNSYears.

What this will do is create a generic query name for your report to use so that any query that you want to use against the report can be used as long as it is given the generic query name.

Note: there are other ways to accomplish what you are doing, such as having code to change the record source of the query in the report itself, but the above may work for you situation.

If my explanation isn't clear, please let me know.

 

[NeilT123]

Thank you sxschech the explanation is very clear unfortunately I have not been.

I have a form that I use to select my reports and once the report and criteria for the report is selected I can then choose to print preview, print to PDF with a preview or print straight to PDF.

There is a query behind my report select form which generates the name of the saved document so I can print straight to PDF and the PDF is saved with the name I need.

What this means is I can't hard code the "print" button with your suggestion.

Can your code be incorporated into the onload of the report or should I be looking for another solution?

Sorry, I should have been more explicit with my info in the 1st place.

 

[sxschech]

Is what you are saying that you don't actually have the year(s) on the form in a combo or text box? They are in the query itself? Perhaps you can provide some of the code for the print button. So I can see how to modify the code for you.

 

[NeilT123]

Thank you sxschech for your help. I have resolved this by changing the way I have stored the original data.