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

 

[MGFoster]

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".

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This happens when you open the query and sort/filter it then close the
query and save the "new" settings. To correct, just open the query and
right click and select "Remove Filter/Sort." Then close and save the
query. In the future when asked if you want to save the query, if you
haven't made any changes to the actual design, click the No button.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSkJqLYechKqOuFEgEQKh0gCg/FFzB0+Ee8ga7BN1hz4NIjgYumsAnjKP
+053Oce6n2apgV+Cney41nuM
=vMH7
-----END PGP SIGNATURE-----

 

[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".