Query requests parameters twice

[SJ]

I have a simple query that asks for a Start Date (Per_Start) and an End Date
(Per_End) for a query, and then checks whether a field is between the Start
Date and End Date (>= [Per_Start] AND <= [Per_End]). When I run it it
correctly asks for the Start Date and End Date, and then asks for them again
for checking. Is there something I'm doing wrong?

 

[Amy Blankenship]

I have a simple query that asks for a Start Date (Per_Start) and an End
Date
(Per_End) for a query, and then checks whether a field is between the
Start
Date and End Date (>= [Per_Start] AND <= [Per_End]). When I run it it
correctly asks for the Start Date and End Date, and then asks for them
again
for checking. Is there something I'm doing wrong?

At some point you probably ran the query, then sorted or filtered the data
and then saved the query. This somehow corrupts the query and there is no
way to fix it--you just have to rebuild the query.

Either that or you didn't use exactly the same expression between the square
brackets. If rebuilding doesn't help, post your entire query.

HTH;

Amy

 

[SJ]

Thanks for your suggestion. So I started a brand new query and kept it as
simple as possible. Am still getting the same 2x thing happening. First it
asks for the [Per_Start] and [Per_End], then it asks for the Record ID, and
then for the Per_Start and Per_End for the date check. It works the way I
want if I enter all the dates twice but seems weird.

Here's the SQL for the query:

SELECT Contacts.RecordID, Contacts.ContactID, Contacts.LastName,
Contacts.FirstName, [LastName] & ", " & [FirstName] AS Name,
Payments.Payment_ID, [Enter Period Start Date for Report: ] AS Per_Start,
[Enter Period End Date for Report: ] AS Per_End, Payments.Pymt_date,
Payments.MIS_Major, Payments.MIS_Minor1, Payments.Pymt_Purpose,
Payments.Pymt_Amount1
FROM Contacts LEFT JOIN Payments ON Contacts.ContactID = Payments.Contact
WHERE (((Contacts.RecordID)=[Enter Record ID: ]) AND
((Payments.Pymt_date)>=[Per_Start] And (Payments.Pymt_date)<=[Per_End]));

I have a simple query that asks for a Start Date (Per_Start) and an End
Date
(Per_End) for a query, and then checks whether a field is between the
Start
Date and End Date (>= [Per_Start] AND <= [Per_End]). When I run it it
correctly asks for the Start Date and End Date, and then asks for them
again
for checking. Is there something I'm doing wrong?

At some point you probably ran the query, then sorted or filtered the data
and then saved the query. This somehow corrupts the query and there is no
way to fix it--you just have to rebuild the query.

Either that or you didn't use exactly the same expression between the square
brackets. If rebuilding doesn't help, post your entire query.

HTH;

Amy

 

[fredg]

Thanks for your suggestion. So I started a brand new query and kept it as
simple as possible. Am still getting the same 2x thing happening. First it
asks for the [Per_Start] and [Per_End], then it asks for the Record ID, and
then for the Per_Start and Per_End for the date check. It works the way I
want if I enter all the dates twice but seems weird.

Here's the SQL for the query:

SELECT Contacts.RecordID, Contacts.ContactID, Contacts.LastName,
Contacts.FirstName, [LastName] & ", " & [FirstName] AS Name,
Payments.Payment_ID, [Enter Period Start Date for Report: ] AS Per_Start,
[Enter Period End Date for Report: ] AS Per_End, Payments.Pymt_date,
Payments.MIS_Major, Payments.MIS_Minor1, Payments.Pymt_Purpose,
Payments.Pymt_Amount1
FROM Contacts LEFT JOIN Payments ON Contacts.ContactID = Payments.Contact
WHERE (((Contacts.RecordID)=[Enter Record ID: ]) AND
((Payments.Pymt_date)>=[Per_Start] And (Payments.Pymt_date)<=[Per_End]));

I have a simple query that asks for a Start Date (Per_Start) and an End
Date
(Per_End) for a query, and then checks whether a field is between the
Start
Date and End Date (>= [Per_Start] AND <= [Per_End]). When I run it it
correctly asks for the Start Date and End Date, and then asks for them
again
for checking. Is there something I'm doing wrong?

At some point you probably ran the query, then sorted or filtered the data
and then saved the query. This somehow corrupts the query and there is no
way to fix it--you just have to rebuild the query.

Either that or you didn't use exactly the same expression between the square
brackets. If rebuilding doesn't help, post your entire query.

HTH;

Amy

1) Name is a reserved Access keyword and should not be used as a field
name. I've changed"Name" to "FullName" below.
For a more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html


2) Remove this part from the SQL:
[Enter Period Start Date for Report: ] AS Per_Start,
[Enter Period End Date for Report: ] AS Per_End,

Then prompt for the [Enter Period ... etc..] in the Where clause, as
done below.

Change your SQL to:

SELECT Contacts.RecordID, Contacts.ContactID, Contacts.LastName,
Contacts.FirstName, [LastName] & ", " & [FirstName] AS FullName,
Payments.Payment_ID, Payments.Pymt_date,
Payments.MIS_Major, Payments.MIS_Minor1, Payments.Pymt_Purpose,
Payments.Pymt_Amount1
FROM Contacts LEFT JOIN Payments ON Contacts.ContactID =
Payments.Contact
WHERE (((Contacts.RecordID)=[Enter Record ID: ]) AND
((Payments.Pymt_date)>= [Enter Period Start Date for Report: ]
And (Payments.Pymt_date)<= [Enter Period End Date for Report: ]));