MAX function is not picking right date in ms access

[Eluruking]

Hi Access Gurus,

Can you please help on the following issue.

We are facing difficulty to find out latest date of each customer. We used
MAX functionality, surprisingly this has given a wrong result. Can you
please assist us to get the desired value. Your fastest reply would really
appreciated. .





Here is the details of the issue

Table: Customer

Format: Text for all fields

Date format in the source table: DD.MM.YYYY

Customers: 301000016, 301000043

Reference
DocumentNo
Doc Date
Net due dt
Customer

GLE-0801
100024068
31.03.2008
07.04.2008
301000016


100024353
07.04.2008
07.04.2008
301000016

ANZBWAUD 08044
1400000301
07.04.2008
07.04.2008
301000016


1200001298
13.03.2009
13.03.2009
301000043


1200011327
13.03.2009
13.03.2009
301000043

CLR OLD PROV
1200012025
01.06.2009
01.06.2009
301000043


1200020506
19.03.2009
19.03.2009
301000043




Following is the result of the query with Max functionality

MaxOfDoc Date
Customer

31.03.2008
301000016

19.03.2009
301000043




The result what we are expecting is:

301000016-07.04.2008

301000043-01.06.2009



Thanks in advance,

Regards,

Eluru

 

[Allen Browne]

Your mistake was to use a TEXT field to store your dates.
Access then sorts/evaluates the field as text.

To solve your problem (plus many others you will have), use a Date/Time
field instead.