Why prompted for parameters multiple times?

[Dorian]

When I run the below query, I get prompted for all 3 parameters multiple
times. Why is this and can I recode the query to avoid this? Thanks.

SELECT * FROM tblCases
WHERE CaseStatus = 'F' AND UnitID = [Unit: 1=Phoenix, 2=Tucson] AND
InvestigationEndDate >= [Investigation End Date from] AND
InvestigationEndDate <= [Investigation End Date to]
ORDER BY CaseNumber DESC;
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[John Spencer MVP]

What do you mean multiple times? Twice? Three Times? Once for each record
in the table?

Is this happening when you run the query or when you use the query in a report?

One way to avoid be prompted is to use a form and controls on the form. You
then reference the controls on OPEN form. You reference them by using
Forms![Name of the Form]![Name of the Control]

So given a form named MyParameters with three controls named Unit, FromDate,
and ToDate, your query becomes

SELECT * FROM tblCases
WHERE CaseStatus = 'F'
AND UnitID = Forms!MyParameters!Unit
AND InvestigationEndDate >= Forms!MyParameters!FromDate
AND InvestigationEndDate <= Forms!MyParameters!ToDate
ORDER BY CaseNumber DESC;


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

 

[Dorian]

Thanks for the response.
I dont know how many times it prompts. After entering the 3 parameters 3
times, I gave up.
I do not want to use a form unless I absolutely have to. These queries are
just opened from a drop down to give the results in a datasheet..
I tried recoding the query as:
PARAMETERS [Unit: 1=Phoenix, 2=Tucson] String, [Investigation End Date
from] DateTime, [Investigation End Date to] DateTime;
SELECT *
FROM tblCases
WHERE CaseStatus = 'F' AND UnitID = [Unit: 1=Phoenix, 2=Tucson] AND
InvestigationEndDate >= [Investigation End Date from] AND
InvestigationEndDate <= [Investigation End Date to]
ORDER BY CaseNumber DESC;

but it failed with 'expression too complex to be evaluated'.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

What do you mean multiple times? Twice? Three Times? Once for each record
in the table?

Is this happening when you run the query or when you use the query in a report?

One way to avoid be prompted is to use a form and controls on the form. You
then reference the controls on OPEN form. You reference them by using
Forms![Name of the Form]![Name of the Control]

So given a form named MyParameters with three controls named Unit, FromDate,
and ToDate, your query becomes

SELECT * FROM tblCases
WHERE CaseStatus = 'F'
AND UnitID = Forms!MyParameters!Unit
AND InvestigationEndDate >= Forms!MyParameters!FromDate
AND InvestigationEndDate <= Forms!MyParameters!ToDate
ORDER BY CaseNumber DESC;


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

When I run the below query, I get prompted for all 3 parameters multiple
times. Why is this and can I recode the query to avoid this? Thanks.

SELECT * FROM tblCases
WHERE CaseStatus = 'F' AND UnitID = [Unit: 1=Phoenix, 2=Tucson] AND
InvestigationEndDate >= [Investigation End Date from] AND
InvestigationEndDate <= [Investigation End Date to]
ORDER BY CaseNumber DESC;
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Dorian]

OK, when I change the >= AND <= to BETWEEN, I only get prompted for the
parameters ONCE.
Interesting...
-- David
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

What do you mean multiple times? Twice? Three Times? Once for each record
in the table?

Is this happening when you run the query or when you use the query in a report?

One way to avoid be prompted is to use a form and controls on the form. You
then reference the controls on OPEN form. You reference them by using
Forms![Name of the Form]![Name of the Control]

So given a form named MyParameters with three controls named Unit, FromDate,
and ToDate, your query becomes

SELECT * FROM tblCases
WHERE CaseStatus = 'F'
AND UnitID = Forms!MyParameters!Unit
AND InvestigationEndDate >= Forms!MyParameters!FromDate
AND InvestigationEndDate <= Forms!MyParameters!ToDate
ORDER BY CaseNumber DESC;


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

When I run the below query, I get prompted for all 3 parameters multiple
times. Why is this and can I recode the query to avoid this? Thanks.

SELECT * FROM tblCases
WHERE CaseStatus = 'F' AND UnitID = [Unit: 1=Phoenix, 2=Tucson] AND
InvestigationEndDate >= [Investigation End Date from] AND
InvestigationEndDate <= [Investigation End Date to]
ORDER BY CaseNumber DESC;
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[John Spencer MVP]

If UnitID is a number field then your query should read

PARAMETERS [Unit: 1=Phoenix, 2=Tucson] Short
, [Investigation End Date from] DateTime
, [Investigation End Date to] DateTime;

If UnitID is a text field that contains number characters then it should read

PARAMETERS [Unit: 1=Phoenix, 2=Tucson] Text (255)
, [Investigation End Date from] DateTime
, [Investigation End Date to] DateTime;

I would try copying the current query string into a NEW query and see if that
cures the problem. If it does - good, then the problem was probably something
to do with your sorting or filtering the query while in datasheet view and
saving the result. If it doesn't than something else is going on and further
investigation on your part will be needed.

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

Thanks for the response.
I dont know how many times it prompts. After entering the 3 parameters 3
times, I gave up.
I do not want to use a form unless I absolutely have to. These queries are
just opened from a drop down to give the results in a datasheet..
I tried recoding the query as:
PARAMETERS [Unit: 1=Phoenix, 2=Tucson] String, [Investigation End Date
from] DateTime, [Investigation End Date to] DateTime;
SELECT *
FROM tblCases
WHERE CaseStatus = 'F' AND UnitID = [Unit: 1=Phoenix, 2=Tucson] AND
InvestigationEndDate >= [Investigation End Date from] AND
InvestigationEndDate <= [Investigation End Date to]
ORDER BY CaseNumber DESC;

but it failed with 'expression too complex to be evaluated'.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

What do you mean multiple times? Twice? Three Times? Once for each record
in the table?

Is this happening when you run the query or when you use the query in a report?

One way to avoid be prompted is to use a form and controls on the form. You
then reference the controls on OPEN form. You reference them by using
Forms![Name of the Form]![Name of the Control]

So given a form named MyParameters with three controls named Unit, FromDate,
and ToDate, your query becomes

SELECT * FROM tblCases
WHERE CaseStatus = 'F'
AND UnitID = Forms!MyParameters!Unit
AND InvestigationEndDate >= Forms!MyParameters!FromDate
AND InvestigationEndDate <= Forms!MyParameters!ToDate
ORDER BY CaseNumber DESC;


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

When I run the below query, I get prompted for all 3 parameters multiple
times. Why is this and can I recode the query to avoid this? Thanks.

SELECT * FROM tblCases
WHERE CaseStatus = 'F' AND UnitID = [Unit: 1=Phoenix, 2=Tucson] AND
InvestigationEndDate >= [Investigation End Date from] AND
InvestigationEndDate <= [Investigation End Date to]
ORDER BY CaseNumber DESC;
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".