Unanswered: RunSQL UPDATE does not transfer Date/Time
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
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.
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.