Query prompts twice for same parameters

T

Tim

In Access 2000 (SP3), running in Windows 2000 Professional SP4) I create and
save a query with user entered parameters. This is for a single table using
the, below, SELECT query. At this point the query runs fine.
Sometimes, not always, cannot figure out why, if I make a change in the
query (e.g. change the which of two fields appears first, or change the
prompt from entering a single date to a between start date and end date) the
query starts asking for the user prompt twice.
The first entry is ignored. Whenever the prompts appear twice, if I enter
different values for the two prompts only the value from the second prompt is
used in executing the query.
The query on which this scenario occured, for both conditions listed:
SELECT DISTINCT Table1.[Serv Nbr] AS [SO #], Table1.EquipID AS SN,
Table1.[Cust Nbr] AS [Customer #], Table1.[Cust Sortname] AS [Customer Name],
Table1.[Close Dt] AS [Closed Date], Table1.[Tech Sortname] AS [Tech Name],
Table1.[Problem Cd]
FROM Table1
WHERE (((Table1.[Closed Date]) Between [Start Date – m/d/yyyy] And [End Date
– m/d/yyyy]))

There have been a number of discussions on this issue where the sort order
was the reason, but there is no sort on this query.
Also, the query will run fine when I open Access then, when I change the
order of appearance for two fields, it will start prompting twice. I will
close Access, restart it and the query will work fine.
Any ideas?
 
D

Duane Hookom

Don't know why you get the multiple prompts. Have you considered upping the
user experience by using controls on a form rather than parameter prompts?
 
T

Tim

I am not very familiar with forms, just started learning about them.
I will try your suggestion though.
Any suggestions or ideas will be very much appreciated.

Thank you,
Tim

Duane Hookom said:
Don't know why you get the multiple prompts. Have you considered upping the
user experience by using controls on a form rather than parameter prompts?

--
Duane Hookom
MS Access MVP
--

Tim said:
In Access 2000 (SP3), running in Windows 2000 Professional SP4) I create
and
save a query with user entered parameters. This is for a single table
using
the, below, SELECT query. At this point the query runs fine.
Sometimes, not always, cannot figure out why, if I make a change in the
query (e.g. change the which of two fields appears first, or change the
prompt from entering a single date to a between start date and end date)
the
query starts asking for the user prompt twice.
The first entry is ignored. Whenever the prompts appear twice, if I enter
different values for the two prompts only the value from the second prompt
is
used in executing the query.
The query on which this scenario occured, for both conditions listed:
SELECT DISTINCT Table1.[Serv Nbr] AS [SO #], Table1.EquipID AS SN,
Table1.[Cust Nbr] AS [Customer #], Table1.[Cust Sortname] AS [Customer
Name],
Table1.[Close Dt] AS [Closed Date], Table1.[Tech Sortname] AS [Tech Name],
Table1.[Problem Cd]
FROM Table1
WHERE (((Table1.[Closed Date]) Between [Start Date - m/d/yyyy] And [End
Date
- m/d/yyyy]))

There have been a number of discussions on this issue where the sort order
was the reason, but there is no sort on this query.
Also, the query will run fine when I open Access then, when I change the
order of appearance for two fields, it will start prompting twice. I will
close Access, restart it and the query will work fine.
Any ideas?
 
D

Duane Hookom

Try this link http://www.fontstuff.com/access/acctut08.htm

--
Duane Hookom
MS Access MVP


Tim said:
I am not very familiar with forms, just started learning about them.
I will try your suggestion though.
Any suggestions or ideas will be very much appreciated.

Thank you,
Tim

Duane Hookom said:
Don't know why you get the multiple prompts. Have you considered upping
the
user experience by using controls on a form rather than parameter
prompts?

--
Duane Hookom
MS Access MVP
--

Tim said:
In Access 2000 (SP3), running in Windows 2000 Professional SP4) I
create
and
save a query with user entered parameters. This is for a single table
using
the, below, SELECT query. At this point the query runs fine.
Sometimes, not always, cannot figure out why, if I make a change in the
query (e.g. change the which of two fields appears first, or change the
prompt from entering a single date to a between start date and end
date)
the
query starts asking for the user prompt twice.
The first entry is ignored. Whenever the prompts appear twice, if I
enter
different values for the two prompts only the value from the second
prompt
is
used in executing the query.
The query on which this scenario occured, for both conditions listed:
SELECT DISTINCT Table1.[Serv Nbr] AS [SO #], Table1.EquipID AS SN,
Table1.[Cust Nbr] AS [Customer #], Table1.[Cust Sortname] AS [Customer
Name],
Table1.[Close Dt] AS [Closed Date], Table1.[Tech Sortname] AS [Tech
Name],
Table1.[Problem Cd]
FROM Table1
WHERE (((Table1.[Closed Date]) Between [Start Date - m/d/yyyy] And [End
Date
- m/d/yyyy]))

There have been a number of discussions on this issue where the sort
order
was the reason, but there is no sort on this query.
Also, the query will run fine when I open Access then, when I change
the
order of appearance for two fields, it will start prompting twice. I
will
close Access, restart it and the query will work fine.
Any ideas?
 
K

Kelly

Hi Tim,
I don't know if I'm too late to help you but I had a similar problem. I had
a query that was working fine and all of a sudden it starting asking for the
parameter values twice. Your comments on sorting helped me figure it out. I
did not have any sort features initially on my query but had recently sorting
the results to view it differently (in datasheet view); when I did this the
parameter problems showed up. I went into design view and added a sorting
order to the fields and the parameter problem went away.
I don't know if this was just luck or what, but you might want to try it.
Kelly
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top