date format

[Guest]

my database gets data from external file (Text, Pipe deliminated).

The date is like " 03122005", "19122005"... How do I convert them into
DD/MM/YYYY format?

there is over 13 m records.

Also after that In the form where its requried... the want the dates in
ascending order?

 

[Douglas J. Steele]

Well, you don't convert to dd/mm/yyyy format: you convert to a date value,
which is stored without any format (internally it's an 8 byte floating point
number, where the integer portion represents the date stored as the number
of days relative to 30 Dec, 1899, and the decimal portion represents the
time stored as a fraction of a day)

To convert the date that you've got, use:

DateSerial(Right([MyDateField], 4), Mid([MyDateField], 3, 2),
Left([MyDateField], 2))

(replace MyDateField with whatever the field is actually called)

Your best bet is to add a new date field to your table, then write an Update
query that uses the formula above to update that new field to the
appropriate date.

Once you've got the date into a table, create a query based on that table,
sort on the Date field you just populated, and use that query wherever you
would otherwise have used the table.