Max date in text

[Dan]

I have a table with one text field

Text
JB10/08/07-1
JB10/05/07-1


I would like JB10/08/07-1 to be the result of the query


I need to have query that will give me the most recent date, but my field
got some addition and therefore causing problem. I tried with max but did not
work - I am guessing because A of August is before M of May.


Any idea ?

 

[scubadiver]

If you want to deal with dates and times, the field must be in date/time
format.

Is it?

 

[Dan]

No, it is not and that is mu problem , even when I use
Format(...,"dd/mm/yy"), the max still does not work properly

 

[scubadiver]

Sorry. Daft question.

What you can do is isolate the date from the rest of the string but then you
would need to be sure it is always preceded by two characters.

Put the following in a query and you will get the date on its own (but do
NOT call it "date". It is a reserved word)

Expr1: Mid([datefield],3,8)

 

[Dan]

I did that but Access still treated it as text, I tried to format it as date
but not better. at the end I have separated day month and year in new fields
and sorted by them.
Thanks

Sorry. Daft question.

What you can do is isolate the date from the rest of the string but then you
would need to be sure it is always preceded by two characters.

Put the following in a query and you will get the date on its own (but do
NOT call it "date". It is a reserved word)

Expr1: Mid([datefield],3,8)


--
www.ae911truth.org

No, it is not and that is mu problem , even when I use
Format(...,"dd/mm/yy"), the max still does not work properly

 

[KARL DEWEY]

Try this --
Expr1: CVDate( Mid([datefield],3,8))

--
KARL DEWEY
Build a little - Test a little

I did that but Access still treated it as text, I tried to format it as date
but not better. at the end I have separated day month and year in new fields
and sorted by them.
Thanks

Sorry. Daft question.

What you can do is isolate the date from the rest of the string but then you
would need to be sure it is always preceded by two characters.

Put the following in a query and you will get the date on its own (but do
NOT call it "date". It is a reserved word)

Expr1: Mid([datefield],3,8)


--
www.ae911truth.org

No, it is not and that is mu problem , even when I use
Format(...,"dd/mm/yy"), the max still does not work properly

:


If you want to deal with dates and times, the field must be in date/time
format.

Is it?


--
www.ae911truth.org



:

I have a table with one text field

Text
JB10/08/07-1
JB10/05/07-1


I would like JB10/08/07-1 to be the result of the query


I need to have query that will give me the most recent date, but my field
got some addition and therefore causing problem. I tried with max but did not
work - I am guessing because A of August is before M of May.


Any idea ?

 

[John W. Vinson]

Put the following in a query and you will get the date on its own (but do
NOT call it "date". It is a reserved word)

Expr1: Mid([datefield],3,8)

Actually, I'd use

Expr1: CDate(Mid([datefield], 3, 8))

to convert the string to a Date/Time field which will sort, format and search
as a date.

John W. Vinson [MVP]