[SQL] Microsoft Access SQL is clipping a string when using INSERT to add a record

I have developed a VBA subroutine to merge a set of similar tables, filter records and do some data cleansing to produce a single table of graduation results. One field is a string containing a date value [Board Date] typically in the format "1 January 1901". When the value is inserted into the database using SQL INSERT, it is being clipped to the first 10 characters. The field is a Short Text field in the database, as are most of the entries. None of the other Short Text fields are being clipped.

For context, I am holding the date as a string because not all source tables have valid date entries. I want to preserve the data so not all entries into this field will be valid dates.

The version of Access being used is Microsoft Access for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20550) 64-bit.

The table definition looks like this:



The line to import the data is:

sql = "INSERT INTO [All Awards] ([ID], [Surname], [Forename], [D-o-b], [Sex], [Hold], [AOS Code], [Year], [Academic Period], " & _
"[Award Type], [Award Title], [Comb stud a], [Comb stud b], [Emphasis], [In], [Hons], [Classification], [Board Date], [Research Title], " & _
"[D-o-b Error], [Board Date Error]) VALUES (""" & _
id_val & """,""" & _
surname_val & """,""" & _
forename_val & """,""" & _
dob_val & """,""" & _
sex_val & """,""" & _
hold_val & """,""" & _
aos_code_val & """,""" & _
year_val & """,""" & _
academic_period_val & """,""" & _
award_type_val & """,""" & _
award_title_val & """,""" & _
comb_stud_a_val & """,""" & _
comb_stud_b_val & """,""" & _
emphasis_val & """,""" & _
in_val & """,""" & _
hons_val & """,""" & _
class_val & """,""" & _
board_date_val & """,""" & _
res_title_val & """," & _
dob_error & "," & _
board_date_error & _
");"
Debug.Print sql
DoCmd.RunSQL sql


The debug printout of the SQL command is:

INSERT INTO [All Awards] ([ID], [Surname], [Forename], [D-o-b], [Sex], [Hold], [AOS Code], [Year], [Academic Period], [Award Type], [Award Title], [Comb stud a], [Comb stud b], [Emphasis], [In], [Hons], [Classification], [Board Date], [Research Title], [D-o-b Error], [Board Date Error]) VALUES ("1234567","Beta","Albert","01/01/1901","M","","190","3","93/94","DOCTOR OF PHILOSOPHY","","","","","","","","01 October 1994","Research title.", False, False);


As you can see, the [Board Date] ("01 October 1994") is intact at this point.

The imported date string in the table looks like this:



At this point, the field has been clipped to the first 10 characters. There are no characters (spaces, etc.) after "01 October".

Does anyone know why this might be happening and how to fix it?

Continue reading...