Results 1 to 15 of 15
  1. #1
    krag is offline Medium experience
    Windows 10 Access 2016
    Join Date
    May 2014
    Posts
    71

    Date format problem in appending data from Access table to sql table

    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.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Dates are stored as a number : dddd.tttt , so no matter what 'format' you display, it should always be a number underneath.
    Tho not sure bout the SQL side storage field, so can you always append to the SQL using the FORMAT property in the query:
    (in query design, select Date fld, properties, format, yyyy-mm-dd)
    (same as using a field: Format([DteFld], "yyyy-mm-dd")
    does this work?

    vs using No format at all. (even tho both tables are using DATE type) ?

  3. #3
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What date data type did you use for your SQL tables?
    DateTime is the preferred one as it definitely works with Access.

    I've seen weirdness when using either Date or DateTime2 data types. Best to avoid.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    krag is offline Medium experience
    Windows 10 Access 2016
    Join Date
    May 2014
    Posts
    71
    Tks Ranman / Minty
    I did start off with DateTime in sql and standard date time (full date and time) in Access.
    Initially it seemed to work fine but suddenly it started showing error of type conversion.
    This was without using format property in append query.
    Please let me know exact property format to use in append query for DateTime in sql.
    Would it be like "yyyy-mm-dd hh:mm:ss"?
    For the date format, my english version of Access and sql show "-" between y, m and d, but my japanese version of access shows "/".
    Of course I am only using the english version of both Access and sql for this project and just mentioned this to understand correct format.

    I also have another issue and perhaps should start a new thread.
    When appending a new record to sql table, I do not use the ID field in append query.
    This also seemed to work fine initially with new ID generated in sql by autonumber.
    Again after a few trials, suddenly it gives error saying that explicit ID must be input in certain cases of ID setting in sql (for ex. replication user).
    I started using a default "0" (zero) and it works for one record to add but obviously next new record also having same "0" ID is not accepted.

    For editing current records in sql table, I bring in the record / records to be edited with their ID's to temp table in access and after editing replace apppend them back to sql without a problem.

    Currently I am the only user for the test process and so not a replication user anyway.

  5. #5
    krag is offline Medium experience
    Windows 10 Access 2016
    Join Date
    May 2014
    Posts
    71
    Today I tried setting date field property in form control, temp table and append query with both "yyyy-mm-dd" as well as "yyyy-mm-dd hh:mm:ss", after setting Date and DateTime respectively in sql.
    It did not work and same type conversion error occurs.
    Both in temp table and append query datasheet view, date was showing in the correspoding format "2018-10-19" and "2018-10-19 00.00.00".
    However, sql format for dateTime is "2018-10-19 00.00.00.000" and Date is "2018-10-19".
    I tried to add ".nnn" to the ss at the end for Date and Time but then it becomes completely different "2018-10-19 00:00::00.000" in access.
    Seems to be leading to nowhere.
    Look forward to your experienced help.

  6. #6
    krag is offline Medium experience
    Windows 10 Access 2016
    Join Date
    May 2014
    Posts
    71
    Another observation on the ID field issue mentioned in earlier post.
    Seems to be linked with the date format issue.
    ID field error seems to be occuring soon after the date type conversion error, when I try to add a new record.
    If I close the Access once and reopen, ID field error does not occur and I can add new record.
    Seems that sql is in a hung status after the date type conversion error.

  7. #7
    krag is offline Medium experience
    Windows 10 Access 2016
    Join Date
    May 2014
    Posts
    71
    When I said adding a new record possible, that is without date fields empty only.
    New record with date field entered, gives same problem.

  8. #8
    krag is offline Medium experience
    Windows 10 Access 2016
    Join Date
    May 2014
    Posts
    71
    Today I tried to avoid the type conversion error, by making a temp table in sql itself.
    Basically its design is identical to the master table but I added a non-identity column ID1.
    Adding new records using this temp table works fine without any type conversion errors for dates (obviously).
    However, when I am trying edit some current records, I followed following steps:
    1. Append the filtered records from master to the empty temp table including the master ID into the ID1 field in Temp table.
    2. Edit the temp table data as required.
    3. Use a join query to pick up the records in master with the ID1 in Temp.
    4. Delete the records in master.
    5. Append the edited Temp records to Master with the ID1 going to Master ID.
    This was working well with Temp table in Access.
    But with Temp table in sql, even though the join query in step 3 is picking up the correct records, it is not allowing to delete.
    If this can be made to work, I could use this method in many other places as well.
    Of course, the speed of processing is slower with this, compared to the Temp table in Access.
    Any ideas are welcome.

  9. #9
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Your attempt to append (Add) a record with an existing PK ID will always fail.
    You can Edit that records data but you can't replace it. This may be the crux of some of your problem.

    Can you post the Access SQL query you are using to update / insert the records?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  10. #10
    krag is offline Medium experience
    Windows 10 Access 2016
    Join Date
    May 2014
    Posts
    71
    Hi Minty
    Tks for yr observation.
    However, I was not trying to add a record with an existing PK ID, if you re-read my earlier post.
    I was first trying to delete the record in master having the same ID as the ID1 in Temp table with following query in access:
    --------
    DELETE Master.*
    FROM Temp LEFT JOIN Master ON Temp.ID1 = Master.ID
    ----------
    When the Temp was in Access, this was possible but when I made the Temp in sql server, it was not able to run the delete query from Access.
    After this step, I was appending the Temp to Master with Temp.ID1 = Master.ID.

    Now after getting your comment, I removed the 2 delete and append querys with a single update query and using WHERE Master.ID = Temp.ID1.
    This is working without any issues.

    Now my only problem seems to be, when Temp is in sql server, processing time is slow for every step.
    This means, I still need to figure out the right way to upload dates from Access to sql without type conversion error.
    When Temp is in sql server, there is no problem at all.

    I can feel that I am almost there but need that last hint from one of you to get over this.
    Otherwise I have to live with the Temp in sql server and running at a lower speed.

    Currently I am testing (learning) the application with SQL Express 2012 free version which was already available at the user's office server.
    However, it is likely that the user will upgrade their server to a latest version or even go for Azure cloud server.
    In such an event, I guess I may have to further tweak the FE and BE with new parameters.
    Your comments on this will also be useful.

  11. #11
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Do you have indexes on the two tables ?

    What is the query that is slow when the Access Temp table is local ?
    And what is the query that gave you the error with the type conversion with the datetime fields ?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  12. #12
    krag is offline Medium experience
    Windows 10 Access 2016
    Join Date
    May 2014
    Posts
    71
    Hi Minty and Ranman
    Sorry for not updating this thread.
    My client's server was attacked by a ransomware and all activity with server came to a standstill as with my testing process.
    Client is reconsidering the whole infrastructure whether to go for cloud server or a brand new local server.
    Also under consideration is to convert my Access-based applications to net-based application.
    An outside IT consultant has warned client that Microsoft may completely do away with Access in near future.
    Anyway, thanks for all your help so far.
    When client is all set up and running again, my project will resume and till then I have to wait and watch.
    For the time being, the original desktop based application I built is being used without any trouble.
    Thanks again

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Krag
    The outside IT consultant would say that.
    MS won't be doing away with Access in the next few years as it has a large user base and fits into their current road plan.
    In fact for the first time in years, it is being updated with new features and fixes for old issues
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #14
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    I am far from a SQL expert but I have used it simply because that is what I was given.

    In all cases SQL was the back end. I did not create Access tables. Instead I used SQL.

    This way there was no need to convert SQL "Dates" to Access "Date" formats.

    As a reminder, Date fields are a special Number regardless of the format.

  15. #15
    krag is offline Medium experience
    Windows 10 Access 2016
    Join Date
    May 2014
    Posts
    71
    Thanks to ridders52.
    That is what I am pushing the client to consider.
    In any case it will cost them considerably to go for a customized solution newly.

    Thanks Rainlover for your comment on date issue.
    I too think date issue can be solved by doing it all in SQL.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 09-27-2016, 01:16 PM
  2. Replies: 5
    Last Post: 03-03-2016, 08:13 PM
  3. Replies: 5
    Last Post: 05-20-2014, 11:51 AM
  4. Replies: 5
    Last Post: 12-09-2012, 02:29 PM
  5. Replies: 1
    Last Post: 03-29-2012, 06:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums