I currently have an imported file from an other database system, the date
structure is coming across in text format as 12th December 2004 etc however I
need to convert this data to short date format but when I do this as part of
the table the system wips my data and I have to import again. Does anyone
have any suggestions on how I can fix.

Based on the quick testing I just did, it's the ordinal information (st, th,
etc) that's causing the problem.

Import the data to a temporary table. Write a function that removes the
ordinal information from the text, then uses CDate() to convert it. Use that
function in a query to convert the date and append to the proper table.

Assuming you're using Access 2000 or newer, the function would look
something like the following untested aircode:

Function ConvertTextDate(TextDate As String) As Date

Dim strDate As String

strDate = Replace(TextDate, "st", "")
strDate = Replace(strDate, "nd", "")
strDate = Replace(strDate, "rd", "")
strDate = Replace(strDate, "th", "")
ConvertTextDate = CDate(strDate)

End Function

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"stefburgas" <(E-Mail Removed)> wrote in message
news:CECFE670-2732-494F-9F6C-(E-Mail Removed)...
>I currently have an imported file from an other database system, the date
> structure is coming across in text format as 12th December 2004 etc
> however I
> need to convert this data to short date format but when I do this as part
> of
> the table the system wips my data and I have to import again. Does anyone
> have any suggestions on how I can fix.

the format (Short Date, Long Date, etc) is immaterial - you can set that any
way you want. the issue is converting a text string to a Date/Time *value*,
which is a numeric value. if the date string is *always* in the format you
posted, you can import the date field as Text, then update the table after
the import, with an Update query, using the following steps:

add an extra field to the table, with Date/Time data type; i'll call it
RealDate. paste the following function into a standard module in your
database. if you create a new module to put it in, do *not* name the module
the same as the function.

Public Function isDate(ByVal strDate As String)

Dim str() As String
str() = Split(strDate)

isDate = CDate(str(1) & " " _
& IIf(Len(str(0)) = 3, Left(str(0), 1), Left(str(0), 2)) _
& " " & str(2))

End Function

run an Update query to update the RealDate field in the table with the
converted date value, as

UPDATE TableName SET TableName.RealDate =
IsDate([TableName].[DateTextField]);

substitute the correct names of the table, the "real date" field, and the
"date as text" field, of course.

hth


"stefburgas" <(E-Mail Removed)> wrote in message
news:CECFE670-2732-494F-9F6C-(E-Mail Removed)...
> I currently have an imported file from an other database system, the date
> structure is coming across in text format as 12th December 2004 etc
however I
> need to convert this data to short date format but when I do this as part
of
> the table the system wips my data and I have to import again. Does anyone
> have any suggestions on how I can fix.