Access query parameters prompting twice
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:
Code:
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:]));
Re: Access query parameters prompting twice
Both come up twice? or just Ending date? I could see ending date prompting twice since u are using it twice...
Re: Access query parameters prompting twice
ok try this:
Code:
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'
Re: Access query parameters prompting twice
You can create parameters instead so you can reference them thoughout your query.
VB Code:
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]));
Re: Access query parameters prompting twice
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!! ;)
Re: Access query parameters prompting twice
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
Re: Access query parameters prompting twice
I have always stuck them in the Criteria Section..... I have some buddies that will be interested in this :)
Re: Access query parameters prompting twice
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
Re: Access query parameters prompting twice
Re: Access query parameters prompting twice
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.
Re: Access query parameters prompting twice