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

  • Thread starter Thread starter ocb
  • Start date Start date
O

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
Click to expand...
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
 
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
Click to expand...
 
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.
Click to expand...


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.
 
Thanks for the reply. I tried you suggestion, but unfortunately got the
same result as before.

Regards

David F Cox skrev:
 
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
Click to expand...

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.
 
Back
Top