Append my new "text" dates to a date field (1 Viewer)

My two column data (date of birth, Deceased Date) come in the form of text "01 Jan 2017"

Through a function and query I have turned it into 01/01/2017.

Data in both fields can be blank

I am trying to append to a date field in another table.

Whilst the results (append) are correct. The append stalls to explain that it can't append all the records in the append query.

set X fields to NULL due to a type conversion failure and it didn't add 0 records key violations, 0 lock violations, 0 validation

Do I want to run the query?

I do but I need this to just run.

My function is this:

I then call it from my query with:

NDOB: IIf(Len([Date of Birth])>0,(Left([Date of Birth],2)) & "/" & CMTN((Right(Left([Date of Birth],6),3))) & "/" & Right([Date of Birth],4),"")

I am thinking that I should format this query to "dd/mm/yyyy".
I have tried to do this in the properties of the field (without sucess) but not on the query formula.

Any pointers would be greatly appreciated.

You can do this more easily using CDate([DOB]) in your query assuming your destination field is DateTime datatype
Similarly for the deceased date

You might need Nz to manage blank records but that's the only complication

If so, your function can be deleted as its no longer needed

Also use Mid function rather than Right(Left... though you no longer need that now

1. Does the field the dates are going into allow nulls?
2. Do you want to append records with null values?
3. Are you sure everything is being turned into a date correctly?
4. That function does not turn anything into a date--it returns at most 2 characters...actually...

Your function is very flawed. The signature line says it returns a string, however if it hits the Else portion it is going to return an integer. I suggest you rewrite your function to actually return a a date or a null. Then depending on the answer to my 3 questions you could do various tests in the query.

Do you really want NULL values? You might consider your requirements and insist on a real date. You can check the date using IsDate() and present a message to the user if needed.

It just seems that when processing and recording dates, you should review the use of Date/Time datatype. Your requirements may differ, but it is often a good practice to use available datatypes where/when practical/appropriate.

Good luck.