Date not sorting

  • Thread starter Thread starter michael c
  • Start date Start date
M

michael c

I have a query that is supposed to be grouping another
query by the first date that the value shows up in the
recordset but the query is making 12/01/03 show up and not
4/01/03. I guess it's reading the 1 in 12 instead of the
4. Any thoughts?
 
I have a query that is supposed to be grouping another
query by the first date that the value shows up in the
recordset but the query is making 12/01/03 show up and not
4/01/03. I guess it's reading the 1 in 12 instead of the
4. Any thoughts?
Click to expand...

Is this a Date/Time field or a Text field? or are you converting a
Date/Time field to a Text value by using the Format() function? Please
post the SQL of your query.
 
John,

My SQL is below. I'm using a data format, so I'm not sure
why it's not sorting.

SELECT qryPhysicianAccountsCourseInfo.SAPNumber, First
(qryPhysicianAccountsCourseInfo.CourseDate) AS CourseDate,
First(qryPhysicianAccountsCourseInfo.CourseLocation) AS
CourseLocation, First
(qryPhysicianAccountsCourseInfo.CourseAttended) AS
CourseAttended
FROM qryPhysicianAccountsCourseInfo
GROUP BY qryPhysicianAccountsCourseInfo.SAPNumber;
 
First() is a pretty useless operator in Access: I've never been able to
figure out why it was included. Try Min() instead.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)
 
Actually, that gave me the first date but it causes my
other two fields to not match up with the min date. I have
a query like so:


SAPNumber CourseDate CourseLocation
GroupBy Min Min


I'm trying to see where each account first attended a
course. Using this method, the course location doesn't
always match up with the course date; it gives me the
alphabetical min value for CourseLocation.
 
Back
Top