Minimum Date not working in Totals Query

[sistemalan]

Hi there,

I am trying to create a query which will show only the oldest child from each family.

I have built a totals query which groups by guardianID (which defines their parent) and shows the minimum date of birth (therefore of the oldest child). Or at least it should. Whenever I view this query it does not show the most hostoric date of birth it shows the earliest one alphabetically. Therefore 04/02/2001 comes before 22/09/1999 and therefore the wrong child is selected.

The DateOfBirth field is absolutely positively a date field.

Any thoughts on how I could fix this problem?

The SQL for the query is as follows:

SELECT Q_AttendingAfterSchool.AdultID, Q_AttendingAfterSchool.AdultName, Min(Q_AttendingAfterSchool.DateOfBirth) AS MinOfDateOfBirth
FROM Q_AttendingAfterSchool
GROUP BY Q_AttendingAfterSchool.AdultID, Q_AttendingAfterSchool.AdultName;

I'm really beginning to pull my hair out with this one. Thanks to anyone taking the time to help.

 

[pbaldy]

Since it looks like Q_AttendingAfterSchool is a query, are you using the Format() function or some other function there that is changing the nature of the field? Various functions can cause a date field to be seen as text. If so, either get rid of it or use CDate() here to force it back to a date.

 

[ABeepath]

You can try the following:- Let me know if it works.

SELECT Q_AttendingAfterSchool.AdultID, Q_AttendingAfterSchool.AdultName,Q_AttendingAfterSchool.DateOfBirth
FROM Q_AttendingAfterSchool
where Q_AttendingAfterSchool.DateOfBirth = (Select Min(Q_AttendingAfterSchool.DateOfBirth) from Q_AttendingAfterSchool);

 

[sistemalan]

Hi both, thanks for your suggestions.

Abeepath, I tried the code you suggested. This returns only one record: the one for the oldest child.

pbaldy, The query Q_AttendingAfterSchool doesn't apply any formatting to the date field, it just returns it as is. The function of the query is just to return children who are currently attending. I'll read up on CDate() just now, hopefully that'll help, regardless of where the problem began.

Cheers,

Alan

 

[sistemalan]

Ooops - It turns out that the date was stored as text, not as a date field in the original table. I could've sworn it was otherwise. Once I sorted that the query worked as planned without having to use CDate.

Next thing I realised is that we had two twins and as they share the same sate of birth they were both returned by the query. I got round this by setting up another totals query, grouping by name.

I now have a query with all the oldest siblings listed together with their families. The next, hopefully not too tricky step is to try and retrieve the names of the oyunger siblings, so that I can mention them in a letter. I've started a different thread about this. Do have a look if you have a moment:

http://www.access-programmers.co.uk/forums/showthread.php?t=191788

Cheers,

Alan