Double entry of parameters used in query

[NuBie via AccessMonster.com]

below is the sql for my query. This works fine however, when i do a DoCmd.
OpenQuery qryFules, acNormal, acEdit I have to enter [Enter Start Date] and
[Enter End Date] twice. I'm wondering why is that, also is there a way to
revise the query in such a way that the dates be entered once?

SELECT ManagerId, FulesData,EXPDate,Category,Organization
FROM Fules
GROUP BY ManagerId,FulesData ,EXPDate,Category ,Organization
HAVING EXPDate Between [Enter Start Date] And [Enter End Date]


Thanks!

 

[Marshall Barton]

below is the sql for my query. This works fine however, when i do a DoCmd.
OpenQuery qryFules, acNormal, acEdit I have to enter [Enter Start Date] and
[Enter End Date] twice. I'm wondering why is that, also is there a way to
revise the query in such a way that the dates be entered once?

SELECT ManagerId, FulesData,EXPDate,Category,Organization
FROM Fules
GROUP BY ManagerId,FulesData ,EXPDate,Category ,Organization
HAVING EXPDate Between [Enter Start Date] And [Enter End Date]

The first thing to do is turn off the Totals option for that
simple query. Then see where things stand.

If the problem still happens, post bace with an explanation
of Fules. I suspect that Fules is a query that also prompts
for the start and end dates.

 

[NuBie via AccessMonster.com]

Thanks Marsh for the reply!!
Fules is a table...
Here's the whole sql of the query: Note there's no Totals nor aggregate
functions used. (I'm wondering too why the OP used GROUP BY and HAVING
clauses)

SELECT ManagerId, FulesData, EXPDate, Category, Organization, [EXP Date]-Now()
AS Days, 30DayNotice, 60DayNotice,Comments
FROM Fules
GROUP BY ManagerId, FulesData, EXPDate, Category, Organization, [EXP Date]-
Now(), 30DayNotice, 60DayNotice, Comments
HAVING EXPDate Between [Enter Begin Date] And [Enter End Date]


The weird thing is when i view the sql of that particular query and copy the
sql then create a new query and paste it. i run the new query and...it asked
only once for start and end dates.

below is the sql for my query. This works fine however, when i do a DoCmd.
OpenQuery qryFules, acNormal, acEdit I have to enter [Enter Start Date] and

[quoted text clipped - 5 lines]

GROUP BY ManagerId,FulesData ,EXPDate,Category ,Organization
HAVING EXPDate Between [Enter Start Date] And [Enter End Date]

The first thing to do is turn off the Totals option for that
simple query. Then see where things stand.

If the problem still happens, post bace with an explanation
of Fules. I suspect that Fules is a query that also prompts
for the start and end dates.

 

[John Spencer]

As a guess, the query was asking twice, because you or someone applied sorting
when the query was in datasheet view.

I BELIEVE THE FOLLOWING:
When this happens the query tends to run twice when you open it in datasheet
view. First time gets the data and opens in datasheet view, and then a second
time that is triggered by the order by property of the datasheet view.

Each time it runs it will ask for the parameters.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks Marsh for the reply!!
Fules is a table...
Here's the whole sql of the query: Note there's no Totals nor aggregate
functions used. (I'm wondering too why the OP used GROUP BY and HAVING
clauses)

SELECT ManagerId, FulesData, EXPDate, Category, Organization, [EXP Date]-Now()
AS Days, 30DayNotice, 60DayNotice,Comments
FROM Fules
GROUP BY ManagerId, FulesData, EXPDate, Category, Organization, [EXP Date]-
Now(), 30DayNotice, 60DayNotice, Comments
HAVING EXPDate Between [Enter Begin Date] And [Enter End Date]


The weird thing is when i view the sql of that particular query and copy the
sql then create a new query and paste it. i run the new query and...it asked
only once for start and end dates.

below is the sql for my query. This works fine however, when i do a DoCmd.
OpenQuery qryFules, acNormal, acEdit I have to enter [Enter Start Date] and

[quoted text clipped - 5 lines]

GROUP BY ManagerId,FulesData ,EXPDate,Category ,Organization
HAVING EXPDate Between [Enter Start Date] And [Enter End Date]

The first thing to do is turn off the Totals option for that
simple query. Then see where things stand.

If the problem still happens, post bace with an explanation
of Fules. I suspect that Fules is a query that also prompts
for the start and end dates.

 

[NuBie via AccessMonster.com]

Thanks John for the insight, thanks to Marsh too.

Thread resolved!!!

MORE POWER TO ACCESS MOSTER

As a guess, the query was asking twice, because you or someone applied sorting
when the query was in datasheet view.

I BELIEVE THE FOLLOWING:
When this happens the query tends to run twice when you open it in datasheet
view. First time gets the data and opens in datasheet view, and then a second
time that is triggered by the order by property of the datasheet view.

Each time it runs it will ask for the parameters.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks Marsh for the reply!!
Fules is a table...

[quoted text clipped - 24 lines]

 

[Marshall Barton]

Thanks Marsh for the reply!!
Fules is a table...
Here's the whole sql of the query: Note there's no Totals nor aggregate
functions used. (I'm wondering too why the OP used GROUP BY and HAVING
clauses)

SELECT ManagerId, FulesData, EXPDate, Category, Organization, [EXP Date]-Now()
AS Days, 30DayNotice, 60DayNotice,Comments
FROM Fules
GROUP BY ManagerId, FulesData, EXPDate, Category, Organization, [EXP Date]-
Now(), 30DayNotice, 60DayNotice, Comments
HAVING EXPDate Between [Enter Begin Date] And [Enter End Date]

The weird thing is when i view the sql of that particular query and copy the
sql then create a new query and paste it. i run the new query and...it asked
only once for start and end dates.

If a query contains a GROUP BY clause, it is a Totals type
query. It's just two different ways (Query Designer and
SQL) of specifying the same things.

The query designer uses the View - Totals menu item to
display the Totals row where you specify the grouping and
associated aggregations. There is a minor flaw in the query
designer that a Totals type query always uses HAVING even
when WHERE is more appropriate/efficient (or sometimes
required).

I see that John has an explanation of why you were being
prompted twice some of the time so I will leave it at that.