I had developed a fairly complex access database for a small enterprise company with several modules of FE and BE for 3 or 4 different users with different tasks but data was all inter-linked.
Now I am testing out (learning) to move the BE table modules to SQL server 2012 Express with the Access 2016.
One of the problems which I could not resolve after trying several ideas is in date format.
I set the sql date format to simple date (yyyy-mm-dd).
I have a temporary table in access where I first create new data or copy specific data from sql table which has to be edited.
Date fields in temp table are short date (mm-dd-yyyy) format.
Data editing / adding is done from a form with the temp table as source.
Date fields are set by a control with date picker and format is short date in the form.
When data from sql for editing is brought into the temp table, the form control displays it as yyyy-mm-dd.
If I save the data back in sql as it is without editing, it goes back without a problem.
But if I change the date or add a new date, it displays as mm-dd-yyyy and when I try to save this with an append query, error message says "field set to null due to type conversion failure".
Append does not work finally.
I need help in converting the format in both directions between sql and access either in query or vba or temp table field property setting.