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.
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:
Code:
Function CMTN(month As String) As String
Select Case month
Case "Jan"
CMTN = "01"
Case "Feb"
CMTN = "02"
Case "Mar"
CMTN = "03"
Case "Apr"
CMTN = "04"
Case "May"
CMTN = "05"
Case "Jun"
CMTN = "06"
Case "Jul"
CMTN = "07"
Case "Aug"
CMTN = "08"
Case "Sep"
CMTN = "09"
Case "Oct"
CMTN = "10"
Case "Nov"
CMTN = "11"
Case "Dec"
CMTN = "12"
Case Else
CMTN = 0
End Select
End Function
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.