finding oldest date from criteria

[Moon]

Hi all,
I have a problem getting the oldest date from a query that returns
results according to the date criteria entered by a user. Here is my
query:


SELECT T1.Key, T1.Age, Min(T1.DatVisit) AS MinOfDatVisit, T1.DatVisit
FROM [Sheet 1] T1
WHERE (((T1.Field1) Is Not Null And (T1.Field1)=0))
GROUP BY T1.Key, T1.Age, T1.DatVisit
HAVING (((T1.Age)>15) AND ((T1.DatVisit)=DMin("DatVisit","T1","Key = "
& [Key])) ((T1.DatVisit) Between [Enter Start Date] And [Enter End Date
]));

This doesn't return the correct results(I'm not getting any errors).
How do I get the oldest date returned from the criteria 'Between [Enter
Start Date] And [Enter End Date ]'?

Thanks so much for any help.
Moon

 

[KARL DEWEY]

Does this work for you?
SELECT T1.Key, T1.AGE, T1.Datvisit
FROM T1
WHERE (((T1.AGE)>15) AND ((T1.Datvisit) Between [Enter Start Date] And
[Enter End Date]) AND ((T1.Field1) Is Not Null And (T1.Field1)=0));

 

[Moon]

Yes that works but how do I get the oldest date if there are several
dates returned for each Key?

 

[KARL DEWEY]

SELECT T1.Key, Min(T1.Datvisit) AS MinOfDatvisit, T1.AGE
FROM T1
WHERE (((T1.Field1) Is Not Null And (T1.Field1)=0))
GROUP BY T1.Key, T1.AGE
HAVING (((Min(T1.Datvisit)) Between [Enter Start Date] And [Enter End Date])
AND ((T1.AGE)>15));

 

[Moon]

Thanks so much for your help!! That worked..
Moon

SELECT T1.Key, Min(T1.Datvisit) AS MinOfDatvisit, T1.AGE
FROM T1
WHERE (((T1.Field1) Is Not Null And (T1.Field1)=0))
GROUP BY T1.Key, T1.AGE
HAVING (((Min(T1.Datvisit)) Between [Enter Start Date] And [Enter End Date])
AND ((T1.AGE)>15));

Yes that works but how do I get the oldest date if there are several
dates returned for each Key?

 

[Moon]

Hi again,
So it looks like the query is not returning some of the rows. I'm sorry
I can't provide concrete examples of this but would you know why this
would be?
Thanks...Moon

Thanks so much for your help!! That worked..
Moon

SELECT T1.Key, Min(T1.Datvisit) AS MinOfDatvisit, T1.AGE
FROM T1
WHERE (((T1.Field1) Is Not Null And (T1.Field1)=0))
GROUP BY T1.Key, T1.AGE
HAVING (((Min(T1.Datvisit)) Between [Enter Start Date] And [Enter End Date])
AND ((T1.AGE)>15));

Yes that works but how do I get the oldest date if there are several
dates returned for each Key?

 

[KARL DEWEY]

You will need to determine which records are not being returned and analyze
what is different about them.

Hi again,
So it looks like the query is not returning some of the rows. I'm sorry
I can't provide concrete examples of this but would you know why this
would be?
Thanks...Moon

Thanks so much for your help!! That worked..
Moon

SELECT T1.Key, Min(T1.Datvisit) AS MinOfDatvisit, T1.AGE
FROM T1
WHERE (((T1.Field1) Is Not Null And (T1.Field1)=0))
GROUP BY T1.Key, T1.AGE
HAVING (((Min(T1.Datvisit)) Between [Enter Start Date] And [Enter End Date])
AND ((T1.AGE)>15));

:

Yes that works but how do I get the oldest date if there are several
dates returned for each Key?