Changing reference cell in query

  • Thread starter Thread starter AJ
  • Start date Start date
A

AJ

Hi!
I have several queries that uses a certain textbox on a form as a parameter
reference. I would like to be able to run these queries mulitple times, but
each time reference a different cell on the form. And without having to type
in the parameters each time. Here is the criteria of my query:

WHERE (((DatePart("q",[Date]))=[Forms]![Quarterly THM
Results]![QuarterStart]) AND ((Year([Date]))=[Forms]![Quarterly THM
Results]![Year1])

I am wondering if there is a way to change this to:

WHERE (((DatePart("q", [Date]))=[Forms]![Quarterly THM Results]![Quarter2]
AND ((Year([Date]))=[Forms]![Quarterly THM Results]![Year2]

Can I do this in a query, or do I have to run some sort of macro to change it?

In the end, I would like to click on 1 button, and have the query run 4
times, and take each of those 4 results and put them onto a report. Any
ideas?
 
not sure I understand what you want....

write the 4 different queries

if the 4 are then in a report all as subreports - the opening of the report
will trigger those queries to fire....
 
Here is the query:

SELECT Max([Sum]) AS [Maximum Total THM]
FROM [potable, THM, all location sums];

here is the [potable, THM, all location sums] query:
SELECT [Sum] FROM [potable, THM, CP LR sum]
UNION SELECT [Sum] FROM [potable, THM, CCR LR sum]
UNION SELECT [Sum] FROM [potable, THM, admin offices sum]
UNION SELECT [Sum] FROM [potable, THM, Mnt Bay Fnt sum]
UNION SELECT [Sum] FROM [potable, THM, CB 1st Flr Fnt sum]
UNION SELECT [Sum] FROM [potable, THM, tool crib sum]
UNION SELECT [Sum] FROM [potable, THM, TransAsh sum]
UNION SELECT [Sum] FROM [potable, THM, CCR Fnt sum]
UNION SELECT [Sum] FROM [potable, THM, Mnt LR sum]
UNION SELECT [Sum] FROM [potable, THM, QA/QC sum]
UNION SELECT [Sum] FROM [potable, THM, Mnt Offices sum]
UNION SELECT [Sum] FROM [potable, THM, HVAC sum]
UNION SELECT [Sum] FROM [potable, THM, lab RO tap sum];

Each individual query (e.g. [potable, THM, CP LR sum]) is based off of
another query which promts the user to input a year and quarter. There is
actually a form that comes up when the button to open the report is clicked,
which has the text boxes [QuarterStart] and [Year1]. Each individual query
references these specific textboxes.

What I would like to do is to run the first query I wrote above, another 3
times, but instead have it reference other cells (e.g. [Quarter2] and [Year2]
etc.) on the form that pops up for opening the report.

Is there code that can be written in another query to change the part of
each query that references [QuarterStart] and [Year1]?

Sorry for the confusing mess of queries :)


GBA said:
not sure I understand what you want....

write the 4 different queries

if the 4 are then in a report all as subreports - the opening of the report
will trigger those queries to fire....



AJ said:
Hi!
I have several queries that uses a certain textbox on a form as a parameter
reference. I would like to be able to run these queries mulitple times, but
each time reference a different cell on the form. And without having to type
in the parameters each time. Here is the criteria of my query:

WHERE (((DatePart("q",[Date]))=[Forms]![Quarterly THM
Results]![QuarterStart]) AND ((Year([Date]))=[Forms]![Quarterly THM
Results]![Year1])

I am wondering if there is a way to change this to:

WHERE (((DatePart("q", [Date]))=[Forms]![Quarterly THM Results]![Quarter2]
AND ((Year([Date]))=[Forms]![Quarterly THM Results]![Year2]

Can I do this in a query, or do I have to run some sort of macro to change it?

In the end, I would like to click on 1 button, and have the query run 4
times, and take each of those 4 results and put them onto a report. Any
ideas?
Click to expand...
Click to expand...
 
Back
Top