Parameter query asking for parameter more than once!

[Trick G]

Hi,

I have a query that requires a Start-Date and an End-Date to be input by user for the Where clause. It is asking for both over and over. I've had it ask from 1 up to 4 times! Shouldn't it store the input and only ask for it once? I'm thinking that the way my query is arranged may be causing it to have to loop through that section more than once to find the data, but that's just my theory. Any help would be great!

Here is my code (abbreviated slightly):

SELECT DISTINCTROW C1.*, C2.*
FROM Pen AS C1 INNER JOIN Jobs AS C2 ON C1.subno=C2.[Jobs Acct]
WHERE ((C1.typ="SS" Or C1.typ="CC" Or C1.typ="PP" Or C1.typ="TT") And C1.stdate>=[Enter Start Date] And C1.stdate<=[Enter End Date] And C2.[Type]<>"EE" And C2.[Type]<>"QQ" And C1.entdate<=C2.[ChangeDate]+60);

I'm selecting rows from "Pen" and "Jobs" that have the same subno/Jobs Acct numbers (text), then there are criteria for "Pen" types, user inputs criteria for date range (Start Date and End Date) and there are criteria for "Jobs" types. Finally, there's a cross-table criteria based on a date field ("Pen" entdate should not be more than 60 days past the "Jobs" ChangeDate). Tables are in quotes in my explanation here.

So running the above, it asks for user input "Enter Start Date", then again for "Enter End Date"...but then it asks for each again...and again...and sometimes again!

Help!

P.S..I didn't notice this repeating until I made it user input (parameter query) because it was using whatever dates I hard-coded in there before.

 

[EMP]

You can use two text boxes (e.g. txtStartDate and txtEndDate) on a form for the user to input two dates and let the user run the query from a command button on the form.

In the query, you can reference the text boxes like this ("Between ... And ..." is more efficient than >= and <=):-

And C1.stdate Between [Forms]![yourFormName]![txtStartDate] And [Forms]![yourFormName]![txtEndDate]

And you can put this line in the On Click event of the command button for the user to run the query:-

docmd.openquery "query name"


On the form, you can even add code to ensure two valid dates are entered in the text boxes before allowing the user to click on the command button to run the query.

^

 

[Trick G]

Thanks EMP

Thank you. I was hoping there was an explanation for this without having to go there, but I'll do what I need to do. I was probably more curious in the "why is this happening" than the fix...inquiring mind and all...but I appreciate the workaround. If anyone reads this thread and has an explanation I'd love to hear it. I'm sure it has to do with the way sql uses the language and if it hits the data twice or more it'll keep asking for input?? I'd guess that a simple one-table query would probably not have this problem...

Thanks again, EMP