Date function within a Union Query

[pmplyons]

I'm building an income statement report in MS Access 2007 and I'm attempting to avoid having to build out sub-reports for each expense/revenue type. I've written a union query in SQL and I'm wondering if I can put in a dim function to avoid having to repopulate my date parameters? For example, I have the following code already working but have to put the begin date for the current year and prior year as well as the end of the measurement period for both dates a number of times. I was hoping I could write a dim function that would require me to only enter the date parameters one time each.

Select Sum(GL_Activity.Amount) AS PTOR


FROM GL_Activity INNER JOIN SAP_GLCodes ON GL_Activity.Account = SAP_GLCodes.Account
WHERE (((GL_Activity.Period) Between [Begin Current Year Date] And [End of Current Period]))
UNION SELECT Sum(GL_Activity.Amount) AS PTOR
FROM GL_Activity INNER JOIN SAP_GLCodes ON GL_Activity.Account = SAP_GLCodes.Account
WHERE (((GL_Activity.Period) Between [Prior Year Begin Date] And [End of Prior Year Period]))
Union SELECT Sum(GL_Activity.Amount) AS ForeignTax
FROM GL_Activity INNER JOIN SAP_GLCodes ON GL_Activity.Account = SAP_GLCodes.Account
WHERE (((GL_Activity.Period) Between [Begin Year Date] And [End of Period]) AND ((SAP_GLCodes.GAAP_PTOR)="ForeignTax"))
Union SELECT Sum(GL_Activity.Amount) AS ForeignTax
FROM GL_Activity INNER JOIN SAP_GLCodes ON GL_Activity.Account = SAP_GLCodes.Account
WHERE (((GL_Activity.Period) Between [Prior Year Begin Date] And [End of Prior Year Period]) AND ((SAP_GLCodes.GAAP_PTOR)="ForeignTax"))
Union SELECT Sum(GL_Activity.Amount) AS FIT
FROM GL_Activity INNER JOIN SAP_GLCodes ON GL_Activity.Account = SAP_GLCodes.Account
WHERE (((GL_Activity.Period) Between [Begin Year Date] And [End of Period]) AND ((SAP_GLCodes.GAAP_PTOR)="FIT"))
Union SELECT Sum(GL_Activity.Amount) AS FIT
FROM GL_Activity INNER JOIN SAP_GLCodes ON GL_Activity.Account = SAP_GLCodes.Account
WHERE (((GL_Activity.Period) Between [Prior Year Begin Date] And [End of Prior Year Period]) AND ((SAP_GLCodes.GAAP_PTOR)="FIT"))
Union SELECT Sum(GL_Activity.Amount) AS SIT
FROM GL_Activity INNER JOIN SAP_GLCodes ON GL_Activity.Account = SAP_GLCodes.Account
WHERE (((GL_Activity.Period) Between [Begin Year Date] And [End of Period]) AND ((SAP_GLCodes.GAAP_PTOR)="SIT"))
Union SELECT Sum(GL_Activity.Amount) AS SIT
FROM GL_Activity INNER JOIN SAP_GLCodes ON GL_Activity.Account = SAP_GLCodes.Account
WHERE (((GL_Activity.Period) Between [Prior Year Begin Date] And [End of Prior Year Period]) AND ((SAP_GLCodes.GAAP_PTOR)="SIT"))

Also, is there a way to return my results horizontally in my union query with a different column name? It would be easier to pull from my report.

Thanks

[pbaldy]

A Dim would only be appropriate in code. For a query, the most common solution would be to have the user enter the dates on a form, and have the query criteria point there.

[rpeare]

Let me add to what pbaldy said. As a guess here if you put in a date you can figure the beginning of the year, and the previous year dates just based on doing one point of data entry so it would not be burdensome for you to create a form and have the other search field values be populated. Then you would reference them in your union query with

[forms]![formname]![searchfieldname]

Where you'd, of course, substitute your form and field names where I have [formname] and [searchfieldname]