UPDATE query to replace 20th character with a "."

[skydivetom]

Hi:

I'd like some feedback about the syntax for an **UPDATE** query which replaces the 20th character (":") with a period (".")

Attached DB contains the following:
- 3 tables each containing a single field [DATETIMESTAMP]


- Across the 3 tables, the data is 100% consistent. That is, each 20th character = ":"
- However, please note that each string value includes 3 colons, but I must ensure to only update the last (3rd) ":"

Eventually, I need to create a function which shall execute the same UPDATE query across n tables. At this point, however, I would just like to establish the proper syntax for the UPDATE query.

Based on [Table1], below are some before/after examples:
1st record (before): 2022/10/12 16:32:37:041
1st record (after): 2022/10/12 16:32:37.041

2nd record (before): 2022/10/12 16:32:37:047
2nd record (after): 2022/10/12 16:32:37.047

3rd record (before): 2022/10/12 16:32:37:052
3rd record (after): 2022/10/12 16:32:37.052

What does the SQL look like for this UPDATE query?

Thanks,
EEH

P.S. Apparently, using the MID function does not work in conjunction with an update query. See attached JPG.

[orange]

Tom,

Here is a sub that will change the Custom "Date" string you receive and store it in a new field that has been set up as DateTime Extended.
I specifically added a new field in Table1 with datatype DateTime Extended. I also went to the table and in Table Fields adjusted the decimal places of this field to 3 (It gives 7 by default).

I could not find info/example to do this with SQL.-- so DAO and recordset.

Reference link 'https://support.microsoft.com/en-us/office/using-the-date-time-extended-data-type-708c32da-a052-4cc2-9850-9851042e0024#bkmk_vba_string

There have been many cautions in the various articles re use of DateTime extended. But if you are happy to move with this, then proceed and verify your processes and any interactions with other applications.

' ----------------------------------------------------------------
' Procedure Name: Tom1
' Purpose: Change custom date [DATETIMESTAMP] as string  to date extended field[moddateTimeX] in table1
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 22-Oct-22
'Reference:
' ----------------------------------------------------------------
Sub Tom1()
10        On Error GoTo Tom1_Error
          Dim db As Database
          Dim rs As Recordset
20        Set db = CurrentDb
30        Set rs = db.OpenRecordset("Table1")
40        Do While Not rs.EOF
50            rs.Edit
                         'date normal yyyy/mm/dd hh:nn:ss     .    3 digit millisec
60            rs!moddatetimex = Left(rs!DateTimeStamp, 19) & "." & Right(rs!DateTimeStamp, 3)
70            rs.Update
80           ' Debug.Print rs!DateTimeStamp & "====>" & rs!moddatetimex
90            rs.MoveNext
100       Loop
110       rs.Close
120       db.Close
          
130       On Error GoTo 0
Tom1_Exit:
140       Exit Sub

Tom1_Error:

150       MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure Tom1" _
         & "  Module  Module1 "
160       GoTo Tom1_Exit
End Sub

Attached is the Updated Database. Table1 has been updated with the DateTime extended value via sub Tom1()

[skydivetom]

Jack:

Wow... your function is FANTASTIC!!!! Yeah!! That certainly will do the trick.

So, going forward, I'll envision do to the following:
1. Run my import as usual
2. Execute Darkwind's TRIM function (posted yesterday). As you know, that re-creates the imported tables but removes all those messy trailing spaces due to uni-compression.
3. Next, execute the attached function which will insert the new field (DateTimeExtended) into the newly created tables (TRIM function). ****
4. Run your function (post #2) to move the dates (text format) into extended date format.
5. Create a new function which then deletes the old date field (text) format from all tables.

*** Note: The attached function ("Insert New Field Into Multiple Tables.accdb") currently uses the following line:

fld.Type = dbText

Naturally, this won't do me any good since I need the db fieldname associated with "extended date time". Do you happen to know what it might be?

Again, your function works fantastic!!! I'm totally excited to be able to use that to correct the data type and use "milli-seconds". And I'm hopeful that the to-be-developed function for deleting all *old dates (text) from all tables (proposed step #5) should be relatively easy as well.

Thank you for the continued assistance. Awesome!!

Cheers,
Tom

[June7]

Mid() certainly can work in UPDATE query. Just your UPDATE makes no sense. Can't update an expression. The Mid() expression is just a string of characters the SQL tries to reconcile as a field name, hence the error message "not a valid name".

Since you have multiple colon characters, a simple Replace() will not work. So the alternative is to parse the date string and rebuild it - which is what Orange's function accomplishes. Could run the expression directly in query without VBA, presuming data is as consistent as you say.

UPDATE table SET DateTimeExt = Left(DateTimeStamp, 19) & "." & Right(DateTimeStamp, 3)

Looping recordset in VBA is one option so is executing an SQL action statement.

CurrentDb.Execute "UPDATE table SET DateTimeExt = Left(DateTimeStamp, 19) & '.' & Right(DateTimeStamp, 3)"

If you are importing data to existing table with data, then probably should limit update to new records so as to not degrade performance.

[skydivetom]

Jack, June7:

thank you both for providing two working solutions. It works great!!!

Very much appreciated!

Cheers,
Tom

[orange]

Hi Tom
Happy to have offered a sample of DateTime Extended populated via a string using a recordset with looping.
As mentioned, there were a lot of cautions re using this datatype. I think you were wise to avoid it, even if the code provided worked. There were just too many gotchas in the related posts/articles etc.
Good luck with your project.