Actually,

=DateSerial(CInt(Left([incdate],2)),CInt(Right([incdate],4)),CInt(Mid([incdate],2,2)))

is incorrect.

DateSerial expects three arguments: year, month and day, in that order.

You're attempting to pass it month, year and day. At least, you would be if

your third argument was correct: you're picking the 2nd and 3rd positions,

which would be correct for 1192008, but then your month would be wrong

(because you're taking the left-most 2 positions)

What would 1 Jan, 2008 be: 112008 or 1012008? If it would be 112008, you've

got problems: would 1222008 be 22 Jan, 2008 or 2 Dec, 2008?

Assuming it would be 1012008, you can change the string to 8 digits using:

Right("00000000" & [incdate], 8)

Now you can use DateSerial:

DateSerial(CInt(Right(Right("00000000" & [incdate], 8), 4)),

CInt(Left(Right("00000000" & [incdate], 8), 2)), CInt(Mid(

Right("00000000" & [incdate], 8), 3, 2)))

Assuming your ShortDate format in Windows is set to mm/dd/yyyy, you can

format the corrected date so that it includes slashes, and use the CDate

function on it:

CDate(Format(Right("00000000" & [incdate], 8), "##/##/####"))

--

Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Tony Williams said:

I have a field that is populated with a text string and I want to convert

this to a date. Anexample of the string is:

1192008

It is in fact a date which should be 01/19/2008 and should be formatted as

mm/dd/yyyy

I have used the Dateserial function as advised by Ken Snell and this is my

interpretation of what he has suggested

=DateSerial(CInt(Left([incdate],2)),CInt(Right([incdate],4)),CInt(Mid([incdate],2,2)))

where incdate is the field holding the data.

However this equation gives me 04/19/178 I wondered whether the proble

could

be that some of the records have a date which is a two digit month such as

11142008 which is then calculated as 11/04/178 where the control is

formatted as mm/yy/dddd

Can someone help please?

Thanks

Tony