Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    34

    Unanswered: RunSQL UPDATE does not transfer Date/Time

    Hey everyone,

    I have a need to move a Time stamp from one table in a database to a table in another database. Both tables are linked to the database that the commands are being run from. They run on a OnClick under Code Editor, which is working fine but presented just in case that's needed to be known. I already have several other UPDATE commands that run and transfer the data fine, but for some reason it does not want to transfer the date. No error is generated, but the date isn't transferred. It will transfer if the destination field is a text format, but not a Date/Time format even though the source is a Date/Time format. Many searches did not come up with the information. If someone can point out the problem (possibly a syntax problem), that would be helpful.

    Here's the layout.

    In the database that the commands run:
    Source Table: [Counts Table] Linked Table
    Source Column: [Scan Date Time] Format: Date/Time
    Destination Table: [Rice flour] Linked Table
    Destination Field: [Extra Field 1] Format: Date/Time

    Code
    DoCmd****nSQL "UPDATE [Counts Table] INNER JOIN [Rice flour] ON [Counts Table].[Sample Tracking ID] = [Rice flour].[Sample Tracking ID] SET [Rice flour].[Extra Field 1] = [Counts Table].[Scan Date Time] WHERE (( [Counts Table].[Dilution]='O'));"
    Note: this may be a forum thing, but for some reason the word run and the period that preceeds it are being replaced with ****. Might be a safety thing that I don't know about.

    I cannot change the formatting of either column (shouldn't need to since they're the same). They have to remain linked tables in the same document. Again, all I want to do is take the date from the Counts table and move it to the Rice flour table. Thanks again for any help.

    David

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    My guess is the query is not updateable. Can you copy the SQL to a query and run it manually? I routinely do this to get better errors from Access.

  3. #3
    Join Date
    Jul 2004
    Posts
    34
    I did as you suggested, and not only did it still show the correct date, but if I ran it as an update query it did update. So i think what I'll do is just save the query then run the query instead of a sql statement. Don't know why the other wouldn't work. Must be one of those computer glitches that are hard to find the answer to. Thanks again.

    David

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •