Access query parameters prompting twice

[salvelinus]

In the following query, the two parameter prompts ("Type the beginning date:", etc), pop up twice. It returns the correct results, but why come up twice? I don't think the last prompt should come up either, since it's the same as the previous one, but at least I'd understand that. I've tried changing parantheses, etc, same thing. Thanks.
I hate working on other people's old rickety code :mad:

SELECT DISTINCTROW [Full Data].[Record Number], [Full Data].Date, ...
FROM [Full Data]
WHERE fldReported = False
AND (([Full Data].Date Between [Type the beginning date:] And [Type the ending date:])
OR (fldCompleted = TRUE AND fldTermDate <= [Type the ending date:]));

[Static]

Both come up twice? or just Ending date? I could see ending date prompting twice since u are using it twice...

[Static]

ok try this:


SELECT DISTINCTROW [Full Data].[Record Number], [Full Data].Date, ...
FROM [Full Data], (SELECT [Type the beginning date:] as BD, [Type the ending date:] as ED FROM [Full Data]) as FD2
WHERE fldReported = False
AND (([Full Data].Date Between FD2.BD And FD2.ED)
OR (fldCompleted = TRUE AND fldTermDate <= FD2.ED));


if u create the prompts in a SELECT qry as part of the FROM u can then refer to them in the WHERE clause with the alias'

[RobDog888]

You can create parameters instead so you can reference them thoughout your query.
PARAMETERS Start_Date DateTime, End_Date DateTime;
SELECT DISTINCTROW [Full Data].[Record Number], [Full Data].Date, ...
FROM [Full Data]...
FROM [Full Data]
WHERE fldReported = False
AND (([Full Data].Date Between [Start_Date] And [End_Date])
OR (fldCompleted = TRUE AND fldTermDate <= [End_Date]));

[Static]

O M G!!! :eek: That is awesome Dog!! all the times I have had issues with parameters i NEVER new you could do something SOOO easy!!

that almost makes me mad!! lol!! ;)

[RobDog888]

Thanks. :)

Easy way to generate them is to go into Access and create a new query. Then right click in the grey area where it usually displays the choosen table(s) and select Parameters from the contexxt menu. Type in a name for your parameter and choose a data type. click the ok button and then view the sql code generated. Its kind of like recording a maco I guess. :D

[Static]

I have always stuck them in the Criteria Section..... I have some buddies that will be interested in this :)

[RobDog888]

Cool, maybe I should add this to my FAQ I have been making for the past few months in between posting. :) Its going to be a general FAQ for all Office apps and its massive. :D

[Static]

Definately!!

[RobDog888]

I just need to pull myself away from posting and helping so I can finish it for about a week or so but too adicted lol.

[Static]

I Know the feeling