Subquery Parameters

  • Thread starter Thread starter Erin Searfoss
  • Start date Start date
E

Erin Searfoss

Hi All,

I have a query [qryDailyBud] which returns each region's name and daily
budget based on the number of working days provided by a subquery
[qryWorkDaysCount]. The subquery requires 2 parameters [BegDate] and
[EndDate]. While qryWorkDaysCount is used for many purposes, when running it
through qryDailyBud these parameters are always "1/1/2007" and "12/31/2007"
so I don't want qryDailyBud to prompt the user for these but would rather
hard code it into the query. What is the syntax for this.

Eventually I would like to enable the user (or another query, form, or
report) to provide a year and use the beginning and end dates or that year as
these two parameters.

Any guidance would be appreciated. Thanks.

Erin

qryDailyBud:
SELECT [qryWorkDaysCount].Region, 10000/[qryWorkDaysCount.WorkDays] AS
FTDailyBud
FROM qryWorkDaysCount;

qryWorkDaysCount:
SELECT tblCalendar.Region, Count(*) AS WorkDays
FROM tblCalendar
WHERE (((tblCalendar.WorkingDay)='Y') AND ((tblCalendar.CalDate) Between
[BegDate] And [EndDate]))
GROUP BY tblCalendar.Region;
 
Assuming you will always be using the current year's data and you want all
data for the year, you can just compare the years in your query

WHERE tblCalendar.WorkingDay)='Y' AND Year(tblCalendar.CalDate) = Year(Date)
 
Thanks. That helps with the second part of the question.

I'm still wondering how to maintain the parameters in qryWorkDaysCount, but
hard code these dates in qryDailyBud and pass them to qryWorkDaysCount.

I need to be able to use qryWorkDaysCount for many uses, not just BOY to
EOY. I only want BOY and EOY when I run it through qryDailyBud.

Klatuu said:
Assuming you will always be using the current year's data and you want all
data for the year, you can just compare the years in your query

WHERE tblCalendar.WorkingDay)='Y' AND Year(tblCalendar.CalDate) = Year(Date)

--
Dave Hargis, Microsoft Access MVP


Erin Searfoss said:
Hi All,

I have a query [qryDailyBud] which returns each region's name and daily
budget based on the number of working days provided by a subquery
[qryWorkDaysCount]. The subquery requires 2 parameters [BegDate] and
[EndDate]. While qryWorkDaysCount is used for many purposes, when running it
through qryDailyBud these parameters are always "1/1/2007" and "12/31/2007"
so I don't want qryDailyBud to prompt the user for these but would rather
hard code it into the query. What is the syntax for this.

Eventually I would like to enable the user (or another query, form, or
report) to provide a year and use the beginning and end dates or that year as
these two parameters.

Any guidance would be appreciated. Thanks.

Erin

qryDailyBud:
SELECT [qryWorkDaysCount].Region, 10000/[qryWorkDaysCount.WorkDays] AS
FTDailyBud
FROM qryWorkDaysCount;

qryWorkDaysCount:
SELECT tblCalendar.Region, Count(*) AS WorkDays
FROM tblCalendar
WHERE (((tblCalendar.WorkingDay)='Y') AND ((tblCalendar.CalDate) Between
[BegDate] And [EndDate]))
GROUP BY tblCalendar.Region;
Click to expand...
Click to expand...
 
Put two text boxes on your form:
txtBegDate and txtEndDate

Then in your query, refer to the text boxes on the form:

WHERE tblCalendar.WorkingDay ='Y' AND tblCalendar.CalDate) BETWEEN
Forms!MyFormName!txtBegDAte AND Forms!MyFormName!txtEndDate

--
Dave Hargis, Microsoft Access MVP


Erin Searfoss said:
Thanks. That helps with the second part of the question.

I'm still wondering how to maintain the parameters in qryWorkDaysCount, but
hard code these dates in qryDailyBud and pass them to qryWorkDaysCount.

I need to be able to use qryWorkDaysCount for many uses, not just BOY to
EOY. I only want BOY and EOY when I run it through qryDailyBud.

Klatuu said:
Assuming you will always be using the current year's data and you want all
data for the year, you can just compare the years in your query

WHERE tblCalendar.WorkingDay)='Y' AND Year(tblCalendar.CalDate) = Year(Date)

--
Dave Hargis, Microsoft Access MVP


Erin Searfoss said:
Hi All,

I have a query [qryDailyBud] which returns each region's name and daily
budget based on the number of working days provided by a subquery
[qryWorkDaysCount]. The subquery requires 2 parameters [BegDate] and
[EndDate]. While qryWorkDaysCount is used for many purposes, when running it
through qryDailyBud these parameters are always "1/1/2007" and "12/31/2007"
so I don't want qryDailyBud to prompt the user for these but would rather
hard code it into the query. What is the syntax for this.

Eventually I would like to enable the user (or another query, form, or
report) to provide a year and use the beginning and end dates or that year as
these two parameters.

Any guidance would be appreciated. Thanks.

Erin

qryDailyBud:
SELECT [qryWorkDaysCount].Region, 10000/[qryWorkDaysCount.WorkDays] AS
FTDailyBud
FROM qryWorkDaysCount;

qryWorkDaysCount:
SELECT tblCalendar.Region, Count(*) AS WorkDays
FROM tblCalendar
WHERE (((tblCalendar.WorkingDay)='Y') AND ((tblCalendar.CalDate) Between
[BegDate] And [EndDate]))
GROUP BY tblCalendar.Region;
Click to expand...
Click to expand...
Click to expand...
 
I'm not sure how this helps me when I'm running qryDailyBud. I currently
don't have a form. I want qryDailyBud to run qryWorkDaysCount with
#1/1/2007# and #12/31/2007# as the parameters w/o asking the user. Is this
possible?

Klatuu said:
Put two text boxes on your form:
txtBegDate and txtEndDate

Then in your query, refer to the text boxes on the form:

WHERE tblCalendar.WorkingDay ='Y' AND tblCalendar.CalDate) BETWEEN
Forms!MyFormName!txtBegDAte AND Forms!MyFormName!txtEndDate

--
Dave Hargis, Microsoft Access MVP


Erin Searfoss said:
Thanks. That helps with the second part of the question.

I'm still wondering how to maintain the parameters in qryWorkDaysCount, but
hard code these dates in qryDailyBud and pass them to qryWorkDaysCount.

I need to be able to use qryWorkDaysCount for many uses, not just BOY to
EOY. I only want BOY and EOY when I run it through qryDailyBud.

Klatuu said:
Assuming you will always be using the current year's data and you want all
data for the year, you can just compare the years in your query

WHERE tblCalendar.WorkingDay)='Y' AND Year(tblCalendar.CalDate) = Year(Date)

--
Dave Hargis, Microsoft Access MVP


:

Hi All,

I have a query [qryDailyBud] which returns each region's name and daily
budget based on the number of working days provided by a subquery
[qryWorkDaysCount]. The subquery requires 2 parameters [BegDate] and
[EndDate]. While qryWorkDaysCount is used for many purposes, when running it
through qryDailyBud these parameters are always "1/1/2007" and "12/31/2007"
so I don't want qryDailyBud to prompt the user for these but would rather
hard code it into the query. What is the syntax for this.

Eventually I would like to enable the user (or another query, form, or
report) to provide a year and use the beginning and end dates or that year as
these two parameters.

Any guidance would be appreciated. Thanks.

Erin

qryDailyBud:
SELECT [qryWorkDaysCount].Region, 10000/[qryWorkDaysCount.WorkDays] AS
FTDailyBud
FROM qryWorkDaysCount;

qryWorkDaysCount:
SELECT tblCalendar.Region, Count(*) AS WorkDays
FROM tblCalendar
WHERE (((tblCalendar.WorkingDay)='Y') AND ((tblCalendar.CalDate) Between
[BegDate] And [EndDate]))
GROUP BY tblCalendar.Region;
Click to expand...
Click to expand...
Click to expand...
 
If you want to include the entire year, then use the code I sent originally.
Other than that, you will have to have a way to enter the dates.
--
Dave Hargis, Microsoft Access MVP


Erin Searfoss said:
I'm not sure how this helps me when I'm running qryDailyBud. I currently
don't have a form. I want qryDailyBud to run qryWorkDaysCount with
#1/1/2007# and #12/31/2007# as the parameters w/o asking the user. Is this
possible?

Klatuu said:
Put two text boxes on your form:
txtBegDate and txtEndDate

Then in your query, refer to the text boxes on the form:

WHERE tblCalendar.WorkingDay ='Y' AND tblCalendar.CalDate) BETWEEN
Forms!MyFormName!txtBegDAte AND Forms!MyFormName!txtEndDate

--
Dave Hargis, Microsoft Access MVP


Erin Searfoss said:
Thanks. That helps with the second part of the question.

I'm still wondering how to maintain the parameters in qryWorkDaysCount, but
hard code these dates in qryDailyBud and pass them to qryWorkDaysCount.

I need to be able to use qryWorkDaysCount for many uses, not just BOY to
EOY. I only want BOY and EOY when I run it through qryDailyBud.

:

Assuming you will always be using the current year's data and you want all
data for the year, you can just compare the years in your query

WHERE tblCalendar.WorkingDay)='Y' AND Year(tblCalendar.CalDate) = Year(Date)

--
Dave Hargis, Microsoft Access MVP


:

Hi All,

I have a query [qryDailyBud] which returns each region's name and daily
budget based on the number of working days provided by a subquery
[qryWorkDaysCount]. The subquery requires 2 parameters [BegDate] and
[EndDate]. While qryWorkDaysCount is used for many purposes, when running it
through qryDailyBud these parameters are always "1/1/2007" and "12/31/2007"
so I don't want qryDailyBud to prompt the user for these but would rather
hard code it into the query. What is the syntax for this.

Eventually I would like to enable the user (or another query, form, or
report) to provide a year and use the beginning and end dates or that year as
these two parameters.

Any guidance would be appreciated. Thanks.

Erin

qryDailyBud:
SELECT [qryWorkDaysCount].Region, 10000/[qryWorkDaysCount.WorkDays] AS
FTDailyBud
FROM qryWorkDaysCount;

qryWorkDaysCount:
SELECT tblCalendar.Region, Count(*) AS WorkDays
FROM tblCalendar
WHERE (((tblCalendar.WorkingDay)='Y') AND ((tblCalendar.CalDate) Between
[BegDate] And [EndDate]))
GROUP BY tblCalendar.Region;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top