Q: Parameter driven queries sometimes ask multiple times...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark - 27 Sep 2005 19:39 GMT

Using Access 2000.

Okay, sometimes I'll write a query that has date parameters, something like
"select * from table where [process_date] between [select start date] and
[select end date]"

[select start date] and [select end date] are parameters where the user
enters the start/end dates.

I've done this query before, no problems. However, on one particular query,
it'll ask the user to supply the start and end dates 3 times. It doesn't
always do this. When I compact the db, it goes back to the regular asking 1
time. But then it goes back to asking 3 times after running the query.

I doubt if this is a query design problem. If it was, the error would be
consistent, but it's not. I'm assuming it's some sort of bug and am wondering
if anyone else has come across it.

Thanks!
-Mark

Reply to this Message

Ofer - 27 Sep 2005 21:43 GMT

Not with this error, but some other strange messages, I found out that the
best thing to do is to create a new query.
Dont copy and paste the sql, write the all thing, it had 90% of success

Signature

I hope that helped
Good luck

> Using Access 2000.
>
[quoted text clipped - 16 lines]
> Thanks!
> -Mark

Reply to this Message

'69 Camaro - 27 Sep 2005 22:09 GMT

Hi, Mark.

> I've done this query before, no problems. However, on one particular query,
> it'll ask the user to supply the start and end dates 3 times. It doesn't
> always do this.

The two most likely reasons you are getting this effect that "accummulates"
over time are:

1.) Track Name AutoCorrect is turned on; (Turn this off!) or

2.) A sort order or filter was applied to the query while it was open, then
the user selected the "Yes" button when prompted to save the changes to the
query. To get rid of these extra prompts, just open the query in Design
View, right click in the upper pane of the QBE grid, and select
"Properties..." from the pop-up menu. Scroll down to the Order By and Filter
Properties and delete these values, then save the form and open it in
Datasheet View again. You should only be prompted once for the parameter --
until someone applies another sort order or filter again.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

> Using Access 2000.
>
[quoted text clipped - 16 lines]
> Thanks!
> -Mark

Reply to this Message

Mark - 28 Sep 2005 00:45 GMT

Thanks! I gotta check out if #2 is the case. Now I actually want to see the
error again...

-Mark

> Hi, Mark.
>
[quoted text clipped - 52 lines]
> > Thanks!
> > -Mark

Reply to this Message