finding oldest date from criteria

  • Thread starter Thread starter Moon
  • Start date Start date
M

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
 
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));
 
Yes that works but how do I get the oldest date if there are several
dates returned for each Key?
 
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));
 
Thanks so much for your help!! That worked..
Moon

KARL said:
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 said:
Yes that works but how do I get the oldest date if there are several
dates returned for each Key?
Click to expand...
Click to expand...
 
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

KARL said:
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 said:
Yes that works but how do I get the oldest date if there are several
dates returned for each Key?
Click to expand...
Click to expand...
Click to expand...
 
You will need to determine which records are not being returned and analyze
what is different about them.

Moon said:
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

KARL said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top