Why does my query ask multiple times for each parameter?

[sueb]

I have the following query:

SELECT IURs.[Receive Date], IURs.[Procedure(s)], IURs.Category, IURs.[Status Date], IURs.Status
FROM IURs
WHERE (((IURs.[Receive Date]) Between [Start Date] And [End Date]) AND ((IURs.Status) Is Not Null) AND ((IURs.Payer)="SA"))
ORDER BY IURs.[Receive Date];
 

which used to work fine. Recently, it has begun asking for a Start Date, and then an End Date, but then it repeats this once or twice. Once it is satisfied, it seems to collect the correct set of records, but what is going on with it?

[NeoPa]

No reason I can see Sue. Are you running it natively? Or is it the .RecordSource for some other object?

[aprpillai]

If you are using this Query as Record Source for your Report it will ask for the parameter values when you preview the Report. When you change from preview to print it will ask for the parameter values again.

[Elizabeth Mitte]

I had this problem and solved it by putting "" around the values I wanted to be shown in the design view of the query. Now it doesn't ask for a parameter. Hope that is possibly helpful!

[sueb]

NeoPa, it's the recordsource only for the one form. And I'm not sure how to know if I'm running it natively; what would I look at to know that?

Elizabeth Mitte, where did you put the quotes? In the criteria string of the query? Currently, my query looks like this:

SELECT IURs.[Receive Date], IURs.[Procedure(s)], IURs.Category, IURs.[Status Date], IURs.Status
FROM IURs
WHERE (((IURs.[Receive Date]) Between [Start Date] And [End Date]) AND ((IURs.Status) Is Not Null) AND ((IURs.Payer)="SA"))
ORDER BY IURs.[Receive Date];
 

Where would the quotes go, exactly?

[sueb]

aprpillai, this query actually supports a form, not a report. And the multiple requests occur before the form is even displayed.

But your statement is curious to me: I've never seen a report ask again for the parameters when I send it to the printer from the preview pane. Why would it do that?

[NeoPa]

Running natively would be simply opening the query from the database window. It shows the results in simple datasheet format. If you're running it as the .RecordSource of an object (which it seems you are - the form being the object), then, as aprpillai suggests, there is scope for the query to require being run more than once. Less so for a form I guess, but an example would be if either the .RecordSource or the .Filter properties were changed at any time after the form is first opened.

[sueb]

NeoPa and aprpillai, yes, it does happen when I run the query directly from the database window.

To be clear: I execute the query from the database window, and, sequentially, it asks for the Start Date and the End Date. It does this repeatedly (sometimes up to a total of four times) before opening the datasheet with the requested records.

[NeoPa]

In that case Sue, I'm at a loss. I have no experience of this type of thing ever happening before. I could only guess at a corrupted database, but that seems unlikely if I'm honest.

[sueb]

Okay, eeryone, thanks for trying, anyway! If I ever do figure out what's gone wrong with this previously-okay query, I'll post back here.

[NeoPa]

Sue, If you like I could take a look at it for you. It would involve posting a database that could be tested by me (any relevant back-end data would need to be available of course). I'll repeat the general instructions for posting a database, even though I know you've seen them before :

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If the database includes any code, ensure that all modules are set to Option Explicit (See Require Variable Declaration).
5. If you've done anything in steps 1 to 4 then make sure that the problem you're experiencing is still evident in the updated version.
6. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
7. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
8. Compress the database into a ZIP file.
9. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[sueb]

Thanks very much, NeoPa! I'll do that. I'm really puzzled by its behavior, so I'll be curious to see if you can find anything. The query is quite simple, so I'm not averse to simply deleting this one and re-creating it (on the assumption that you're right about some little corruption somewhere), but I'd really love to know if there's actually something incorrect.

[NeoPa]

One of the things I certainly plan to do is to delete and recreate the main elements of the problem. Probably the tables and the query. Whether or not the new versions exhibit the same properties in this regard as the old, is something I'd like to know.

[NeoPa]

This is bizarre!

I found no reason or data corruption anywhere. I tried various approaches, even deleting all data from the table (as well as creating a new table and stripping out all unused fields, creating various new queries pointing to the same and copies of the table). None worked.

I found the fix was to remove the WHERE clause (in the design view worked fine) entry for the Between [Start Date] And [End Date], then simply to re-apply it. What was strange was that I didn't even have to retype it. Simply pasting back in the same text I'd Cut out worked perfectly well.

When I had consistently working, and non-working, versions of the query, I also compared the SQL of each, as well as each of the visible Query properties. I saw nothing different between the two queries at any point.

[NeoPa]

Just to explain :

I originally posted this response in one of your other threads, after the instructions you left there seemed to indicate this was the problem I should be looking at. Having worked on both threads I found it simple to confuse one with the other.

Anyway, this latest post is a response that should be in this thread (so I've moved it across). I will have another look at the other thread, but you may find your notification indicating there is a reply there which is not there any more.

[sueb]

Well, once again you're right, NeoPa!

Removing and replacing that text made it work correctly again. How ever did you even stumble on this?? What made you think of trying that?

[NeoPa]

When objects are creeated based on text strings (as queries are) they sometimes get out of synch, so a reboot fix is to type the string in again. In this instance it worked ;-)

[sueb]

Wow. So that's something I'll tuck away to remember for future bizarre query problems. Thanks, NeoPa!

[JDyer]

There are cases where this occurs when the parameter criteria is not used with the Where for the fields used with your params.

[NeoPa]

I'm afraid that you seem to have the wrong end of the stick entirely. That is clearly not the issue in this case, as has already been demonstrated by the existing posts in the thread.

We don't have rules against resurrecting old threads per se, but please read through them before offering advice that may be inappropriate, as this seems to be. Bear in mind also that a picture of the query in design view is not too helpful when much of the information is truncated. Typically we use the SQL of a query as that encapsulates all the logic completely.

The issue here was found to be a glitch or corruption in the database, and not related to any faulty SQL. When the SQL was re-applied, exactly as it was originally, it worked fine. Clearly there was nothing wrong with the original SQL itself.