How do I get Min and Max to work with international date format?

[ocb]

I have a table t containing results from sailing competitions (20k+
records) with the fields

t.ID t.BoatID t.RaceDate t.Result (one record per boat per race)

t.RaceDate has the date format dd.mm.yyyy, the earliest being around
year 1880

From what I'm read (i.e

http://groups.google.no/group/microsoft.public.access.queries/msg/6297ede6849e150d),
the JET engine expects dates to be formatted as mm/dd/yyyy. When
querying for the first and last date a boat has competed, the result is
often wrong.

What is the best solution to this problem? I've tried
Format(t.RaceDate,"mm.dd.yyyy"), but this only seems to format the date
AFTER the query has been executed.

Regards

 

[David F Cox]

DateValue(Replace("01.01.1887",".","/"))

I have a table t containing results from sailing competitions (20k+
records) with the fields

t.ID t.BoatID t.RaceDate t.Result (one record per boat per race)

t.RaceDate has the date format dd.mm.yyyy, the earliest being around
year 1880

http://groups.google.no/group/microsoft.public.access.queries/msg/6297ede6849e150d),
the JET engine expects dates to be formatted as mm/dd/yyyy. When
querying for the first and last date a boat has competed, the result is
often wrong.

What is the best solution to this problem? I've tried
Format(t.RaceDate,"mm.dd.yyyy"), but this only seems to format the date
AFTER the query has been executed.

Regards

 

[Marshall Barton]

I have a table t containing results from sailing competitions (20k+
records) with the fields

t.ID t.BoatID t.RaceDate t.Result (one record per boat per race)

t.RaceDate has the date format dd.mm.yyyy, the earliest being around
year 1880

http://groups.google.no/group/microsoft.public.access.queries/msg/6297ede6849e150d),
the JET engine expects dates to be formatted as mm/dd/yyyy. When
querying for the first and last date a boat has competed, the result is
often wrong.

What is the best solution to this problem? I've tried
Format(t.RaceDate,"mm.dd.yyyy"), but this only seems to format the date
AFTER the query has been executed.

The Format if displayed dates is not relevant, it the type
of the field in the table that matters. Double check if the
RaceDate field is a Date/Time field and not a Text field.

 

[ocb]

Thanks for the reply. I tried you suggestion, but unfortunately got the
same result as before.

Regards

David F Cox skrev:

 

[DaveP]

I have a table t containing results from sailing competitions (20k+
records) with the fields

t.ID t.BoatID t.RaceDate t.Result (one record per boat per race)

t.RaceDate has the date format dd.mm.yyyy, the earliest being around
year 1880

http://groups.google.no/group/microsoft.public.access.queries/msg/6297ede6849e150d),
the JET engine expects dates to be formatted as mm/dd/yyyy. When
querying for the first and last date a boat has competed, the result is
often wrong.

What is the best solution to this problem? I've tried
Format(t.RaceDate,"mm.dd.yyyy"), but this only seems to format the date
AFTER the query has been executed.

Regards

What I have done in the past is format my dates using dd/mmm/yyyy.
This gets around the confusion of dd & mm. Check out Allen Browne's
site for a good explanation on date format pitfalls -
http://allenbrowne.com/ser-36.html

HTH

David.