finding oldest date from criteria

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
 
K

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));
 
M

Moon

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

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));
 
M

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?
 
M

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

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?
 
K

KARL DEWEY

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top