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?

The short answer to my question is probably "user error" or "user ignorance". The slightly longer answer is that I have inadvertently changed the SIZE parameter for the Short Text fields and this is leading to entered values being truncated. If this happens to you then check the SIZE parameter in the table design view for the given field. Thanks to Erik A and iDevelop for steering me in that direction.

I did a little bit of an experiment with a new blank database and found the following:

If you create a new Short Text field it will default to 255 characters. If, in the current session, you change that field to a Long Text, save the database, and then change it back to a Short Text, it will revert to being 255 characters long. The image below shows the "after" view of this change.

However, the behaviour is different if you make this change across multiple sessions. First, I changed a Short Text field to a Long Text, saved the database and closed it. I then re-opened the database and changed the field back to a Short Text. In this case, it changed to being 50 characters long, not 255. The image below shows the "after" view of this change.

This doesn't explain why my field was set to 2 characters, hence the explanation of user error, but I think it is worth noting that Short Text field sizes can change in a counter-intuitive way if you are making changes to the database design across sessions.