Another CDate() question

Hi All, Its been a while



I Keep getting an #Error result on the CDate function



1st step works fine

txtBookedDate: Trim(Mid([BookingDumpScreen],InStr([BookingDumpScreen],"P.")+4,8))

Answer as expected 07JAN15



2nd step converted the text to date

Tried BookedDate: CDate([txtBookedDate])

Answer #Error

Then tried BookedDate: CDate(Format([txtBookedDate],"dd/mm/yyyy"))

Answer #Error



I have tried so many combinations I have lost count. Even tried splitting the 07JAN15 date into a Day field then a Month and year field then build them all back together as dd/mm/yyyy see below



DateBookedDay: Left([txtBookedDate],2)

DateBookedMonth: Mid([txtBookedDate],3,3)

DateBookedYear: Mid([txtBookedDate],6,3)

DateBookedBuild: [DateBookedDay] & "/" & [DateBookedMonth] & "/" & [DateBookedYear]

BookedDate: CDate([DateBookedBuild])



This gives me as required 07/01/2015 in the query and when i select the BookedDate value the little calendar icon appears which I presume confirms it’s classed as a date field.

When I convert the select query over to an Append query and assign the BookedDate to the required table field and run it I get an Enter Parameter window asking for the DateBookedBuild value which i didn't get when it was a select query.



I feel I have over complicated a simple matter? Help… what I am I missing?



Thanks

Another CDate() question