id arrival_date AssumedFormat [b]UKDates[/b]
1 6/20/2004 US Format 20/06/2004
..................................................
635 5/15/2005 US Format 15/05/2005
636 3/3/2005 US Format 03/03/2005
637 4/3/2005 US Format 03/04/2005
638 4/4/2005 US Format 04/04/2005
639 5/4/2005 US Format 04/05/2005
640 5/3/2005 US Format 03/05/2005
645 05/6/2005 UK Format 05/06/2005
646 07/03/2005 UK Format 07/03/2005
647 07/03/2005 UK Format 07/03/2005
648 08/03/2005 UK Format 08/03/2005
649 03/08/2005 UK Format 03/08/2005
650 03/08/2005 UK Format 03/08/2005
651 03/11/2005 UK Format 03/11/2005
652 14/03/2005 UK Format 14/03/2005
653 03/20/2005 UK Format 20/03/2005
654 15/03/2005 UK Format 15/03/2005
..................................................
869 17/06/2005 UK Format 17/06/2005
870 13/06/2005 UK Format 13/06/2005
871 13/06/2005 UK Format 13/06/2005
Changing ID#>640 to ID#>0 in Jon K's query was the same as assuming that the records in the table were all UK text dates, which of course would not work.I did tried with ID#>0 but with this query all the missalaneous formats of dates gets mixed up and doesn't corelate with the desired result
id arrival_date Assume US Assume Mashed
76 10/23/2004 23-Oct-04 23-Oct-04
77 10/30/2004 30-Oct-04 30-Oct-04
81 5/11/2004 11-May-04 05-Nov-04
94 6/11/2004 11-Jun-04 06-Nov-04
105 7/11/2004 11-Jul-04 07-Nov-04
106 8/11/2004 11-Aug-04 08-Nov-04
115 9/11/2004 11-Sep-04 09-Nov-04
116 10/11/2004 11-Oct-04 10-Nov-04
171 12/11/2004 11-Dec-04 12-Nov-04
172 11/13/2004 13-Nov-04 13-Nov-04
193 12/20/2004 20-Dec-04 20-Dec-04
194 11/20/2004 20-Nov-04 20-Nov-04
216 1/12/2004 12-Jan-04 01-Dec-04
217 12/1/2004 01-Dec-04 12-Jan-04
223 1/12/2004 12-Jan-04 01-Dec-04
225 12/1/2004 01-Dec-04 12-Jan-04
229 11/16/2004 16-Nov-04 16-Nov-04
230 12/1/2004 01-Dec-04 12-Jan-04
231 11/16/2004 16-Nov-04 16-Nov-04
232 12/2/2004 02-Dec-04 12-Feb-04
Originally Posted by Jon K
Then the date specification might have been switched more than once in the past.
In that case, I would add a field in the table to manually flag either the UK dates or the US dates, depending on whichever are fewer.
And then base the query expression on this flagged field instead of the [ID] field.
Using CDate() on the whole column on a UK system would erroneously convert any US dates where the first two numbers are less than 13 (e.g. 3/11/2005 i.e. March 11) to UK dates (i.e. 3 November.)
.
id arrival_date [b]USDate [color=red]UKDates[/color] Converted
ThatNeeds
Conversion[/b]
76 10/23/2004 0 23/10/2004
77 10/30/2004 0 30/10/2004
81 5/11/2004 0 05/11/2004
94 6/11/2004 0 06/11/2004
105 7/11/2004 0 07/11/2004
106 8/11/2004 0 08/11/2004
115 9/11/2004 0 09/11/2004
116 10/11/2004 0 10/11/2004
171 12/11/2004 0 12/11/2004
172 11/13/2004 0 13/11/2004
193 12/20/2004 0 20/12/2004
194 11/20/2004 0 20/11/2004
216 1/12/2004 0 01/12/2004
217 12/1/2004 -1 01/12/2004 Converted
223 1/12/2004 0 01/12/2004
225 12/1/2004 -1 01/12/2004 Converted
229 11/16/2004 0 16/11/2004
230 12/1/2004 -1 01/12/2004 Converted
231 11/16/2004 0 16/11/2004
232 12/2/2004 -1 02/12/2004 Converted