Query repeatedly asking for parameters

[Thirith]

I'm currently working towards a report for a database I put together. I've created a simple query with all the relevant values from the main table; since I want to be able to filter by date, I've added the following to the criteria field for SubmissionDate: "Between [Enter start date:] And [Enter end date:]".

This works fine the first time I run the query, but if I run it a second time and enter new values (or even the same ones), I keep getting asked to enter the start date and end date. I never actually get to the datasheet. If I click on "Cancel" instead, I get an empty list.

Any idea what might be the issue here? It seems pretty darn strange to me - but then I haven't really worked with query parameters in a long time.

 

[Thirith]

Quick update/correction: Actually, I only need to enter the dates twice. Still weird and a bit annoying, but once I've entered each date twice (first the start date, then the end date, then the whole thing again) I get the correct results.

 

[stumac]

Can you post the query SQL?

 

[Thirith]

Here it is:

SELECT tblScientificExchanges.SE_ID, tblScientificExchanges.DateSubmission, tblScientificExchanges.EventOrVisit, tblScientificExchanges.Decision, tblScientificExchanges.ParticipantNumber, tblScientificExchanges.RequestedSum, tblScientificExchanges.DateDecision, tblScientificExchanges.Referee, tblScientificExchanges.ApplicantInstitutionFROM tblScientificExchanges
WHERE (((tblScientificExchanges.DateSubmission) Between [Enter start date:] And [Enter end date:]));

 

[stumac]

Have you set up the parameters? If they aren't exactly the same it would ask for them twice.... I tend not to set them up and just include them in the query.

There have been other issues in the past that have caused this issue (I think something to do with sorting). Best way to stop it is to delete the query, paste your SQL into a new one and save it.... The SQL looks fine though.

 

[Thirith]

It'd be easy enough to put the parameters in the query and just change them on the fly, though in the best of all possible worlds I'd want to set things up so I can automatise reports based on start/end date. At the same time, since we'll probably do a yearly evaluation, I'm not sure it's worth putting in the time to create a nice report; it's probably more efficient just to run the query and then copy the data over to Excel and work with them there.

 

[xenou]

By set up the parameters I think it means to check the parameter dialog to see if you have the parameters defined. Maybe you already have defined parameters such as [Enter Start Date] instead of [Enter Start Date:]

Personally I've never met a query that works once but not twice. Probably I'd delete it and recreate it and cross fingers.

 

[stumac]

By set up the parameters I think it means to check the parameter dialog to see if you have the parameters defined. Maybe you already have defined parameters such as [Enter Start Date] instead of [Enter Start Date:]

Personally I've never met a query that works once but not twice. Probably I'd delete it and recreate it and cross fingers.

Yeah - sorry I did mean define the parameters.

I was able to recreate the issue by setting an Order BY property on the query - removing this caused the prompt only to show once.

 

[xenou]

That's interesting (sound buggy?). Anyway you could retain the Order By probably by wrapping the query:


i.e, if Query1 is the current query then query it from Query2, as below:

SELECT q1.F1, q1.F2, q1.F3
FROM Query1 q1
ORDER BY q1.F1;

Obviously you could also do the sorting in Excel or (I think) even in a report, wherever the final output is.

 

[stumac]

Should be fine with an order by clause in the query, it's if you set the order by on the query properties pane. Looks like a known 'issue' from a quick Google search.