Query to evaluate two fields based on one user-defined date range 1

[cdck]

I have the following query:

[tt]
SELECT DISTINCT D.fdate, E.fempno, E.ffname, E.fname, E.fendate
FROM dbo_lamast AS D, dbo_PRempl AS E
WHERE (((D.fdate) Between [Start:] And [End:] And (D.fdate) Not In (SELECT fdate FROM dbo_lamast WHERE fempno = E.fempno)) AND ((E.fendate)=#1/1/1900#))
ORDER BY D.fdate, E.ffname, E.fname;
[/tt]

My problem is that I want the user to enter the date range only once, and then the query to use that date range to evaluate not only the fdate field, but also the fendate field; ((E.fendate)=#1/1/1900#) OR (fend > [Start:]). How can I do this?



Cheryl dc Kern

 

[Remou]

How about a parameter?

 

[cdck]

I don't understand what you're referring to. How do you set parameters? Is there a way for a user to set one each time they open the query?

Cheryl dc Kern

 

[cdck]

(I thought I was already working with parameters by having the query ask them for the start and end dates - the issue is I don't want them to ask for the start a second time so that it can use the same date to evaluate a second field)

Cheryl dc Kern

 

[Remou]

You can add parameters to a query like so:

[tt]PARAMETERS [Start:] DateTime;
SELECT DISTINCT D.fdate, E.fempno, E.ffname, E.fname, E.fendate
FROM dbo_lamast AS D, dbo_PRempl AS E
WHERE (((D.fdate) Between [Start:] And [End:] And (D.fdate) Not In (SELECT fdate FROM dbo_lamast WHERE fempno = E.fempno)) AND ((E.fendate)=#1/1/1900#))
ORDER BY D.fdate, E.ffname, E.fname;[/tt]

Which is nice, or you can simply refer to [start:] again, the user will only be asked once. Another option is to use a form for such details, this is an often recommended option.

 

[cdck]

Thank you for the clarification. I never encountered that before!

Cheryl dc Kern