Date issue where *string* (hh:nn) equals "23:54" (vs. "00:54" or 12:54 AM)

[skydivetom]

Hello Experts:

In a previous thread (Need to trim *all fields* across *all tables* (accessforums.net) -- post #49), I received some wonderful assistance from forum expert @darkwind. With the help of darkwind, we established routine which does the following:

1. Function reads-in a date-time value (stored as *short text*) and converts it into a date-time data type.
2. As part of this routine, it also strips out the "milli-seconds" and places them into a numeric field.

Example (before):
- Short Text = "2018/10/29 14:08:06:112"

Example (after):


- Date/Time = "2018/10/29 14:08:06"
- Number = "112"

Again, the function (which is not included in the sample DB) works really well with my sample data. However, once I applied it to my actual data (thousands of records), I have noticed an issue w/ a sub-set of the underlying data (not the function).

I'll summarize the *data* issue below:
- Attached DB includes 2 tables each of them containing a single field [FIELD2].
- Both tables contain date-time related content but (again) these are stored in "Short Text" data format.
- While [Table1] is "clean" (so to speak), [Table2] contains some erroneous dates... these are a non-issue while in string format. However, they turn out to be an issue when running the function.
- Specifically, the 2nd record in [Table2] = "2019/10/26 24:54:30:949".
- Well, there is no such things as hh/nn being equal to "24:54". It would be either "23:54" or "00:54" (aka 12:54 AM).
- Note: I am NOT the data owner so I cannot speak as to how this happened in the source system.

What I need some assistance with:
========================
- Develop a method (query or VBA) which -- before running my function -- would identify & clean any erroneous hh:nn (while still being in *string* format).

Business Rule:
- Scan through all tables that have an "801_" prefix.
- Next, although my actual tables have more than a single field, parse through only [FIELD2] and
- ... where any hh:nn value is >= 24:00 (e.g., "24:01" or "24:59") then
- change the string value day by adding 1 to the day AND subtracting "24" from the hour value.
- For instance, erroneous value = "2019/10/26 24:54:30:949" should change to string value "2019/10/27 00:54:30:949"
- The latter, however, would be inconsistent in the event the erroneous string value = 10/31/2022 24:01 since there's no such thing as "10/32/2022 00:01".

The above business rule is only meant to be a conceptual scenario. There may be a much easier way to do this. But do keep in mind that no arithmetic can be done w/ the [FIELD2] given it must stay "short text" (for now). The conversion into an actual date will occur w/ my existing function.

Any thoughts on how to clean up the erroneous hh:nn into an acceptable format so that my existing function won't fail during the conversion process?

Thank you,
Tom

[accesstos]

Hi to all!

Tom,

Give a try with this query:

SELECT [801_Table2].FIELD2, DateValue(Left([FIELD2],10))+TimeSerial(Mid([FIELD2],12,2),Mid([FIELD2],15,2),Mid([FIELD2],18,2))+((Mid([FIELD2],21,3))/84000000) AS DTM
FROM 801_Table2;

As you see, TimeSerial() is very "elastic" with houres >23.

Also, I suggest to you to leave the data in table(s) as it is. Do not make updates, just pull the info that you want, as you want on query level.

Cheers,
John

[skydivetom]

Hi John:

thank you for your assistance as well as this query. The query itself works perfectly and does implement the proposed business rules exactly as envisioned.

However, as mentioned in the original post, I must maintain the original *string* format (i.e., "identify & clean any erroneous hh:nn (while still being in *string* format)".

Here's why:
- The existing TRIM function does do the following:
- "Looks" for a text field in the format "####/##/## ##:##:##:###*" (see code snippet below):

                  'Test first field value against format
                  If fld.Value Like "####/##/## ##:##:##:###*" Then
                     
                     'If datetime contains milli-seconds (MS), split into two fields
                     sSQLCreateTable = sSQLCreateTable & fld.Name & " " & ToName(dbTimeStamp) & ","
                     sSQLCreateTable = sSQLCreateTable & fld.Name & "Ms " & ToName(dbInteger) & ","
                     
                     sSQLInsertData = sSQLInsertData & "CDate(Left([" & fld.Name & "], 19)),"
                     sSQLInsertData = sSQLInsertData & "CInt(Right([" & fld.Name & "], len([" & fld.Name & "]) - 20)),"
                     
                     sFieldList = sFieldList & "[" & fld.Name & "],"
                     sFieldList = sFieldList & "[" & fld.Name & "MS],"
                  Else

- Ultimately, 100% of FIELD2 values match that criteria/condition.
- Therefore, for that particular field, the conversation strips out the *date* portion and inserts the date into an actual date field.
- Further, the last 3 digits (in the string) represents the milli-seconds. These are then inserted into a new numeric field.
- At the end of the TRIM function, the original table is deleted and the new table is renamed to its original table name.
- All credit should be given to forum expert @darkwind who developed this conversion function.

All that said, I literally must maintain the original string format so that the conversion function (string to date AND number) will continue to work. Also, in the query, I may have lost the "milli-seconds" component which is a critical element in the data analysis.

Recap:
- Your query works perfectly to identify and change the invalid dates into correct/desired dates.
- At the same time, the query's conversion into already a date format (vs. keeping it string and maintaining the milli-seconds) will cause a secondary, undesired effect.

Follow-up Question:
Is there any way to implement your logic (business rule) w/o changing the format. For example, the query changes the 2nd record (invalid) from/to:
from: "2019/10/26 24:54:30:949" to: "10/27/2019 12:54:31 AM" ... it is the correct logic but in an undesired format.

Instead, the preferred output would be:
from: "2019/10/26 24:54:30:949" to: "2019/10/27 00:54:30:949" ... same logic as yours but in a different (desired) format

Thank you!

[accesstos]

You are welcome Tom!

As long as you have a date/time value, you can format it as you want. For example, by using the query below:

SELECT FIELD2, 
Format(DateValue(Left([FIELD2],10))+TimeSerial(Mid([FIELD2],12,2),Mid([FIELD2],15,2),Mid([FIELD2],18,2)),"yyyy/mm/dd hh:nn:ss." 
& Mid([FIELD2],21,3)) AS DTMS_TXT
FROM 801_Table2;

we take the results:


So you can run an update query before the TRIM process (although in my opinion you could avoid it entirely as I mentioned) to replace the values ​​of FIELD2 with the values ​​of the DTMS_TXT field.

The bad news is that in Access, we can't display a date/time value in a format with milliseconds. Such an attempt will convert the date/time value to a string. The good news is that even if they don't appear, the milliseconds are counted in the date/time value (Access rounds up the seconds if the milliseconds are more than 500). So, you don't loose the "milli-seconds" component and (at least internally) your data analysis procedures will work properly with the precision of millisecond.

Cheers,
John

[skydivetom]

John:

Wow... your solution is perfect!!!!

In order to keep the same exact format. I only to replace the last "." (before the milli-seconds) with another colon. That's the format all other text date/time are stored.

Excellent, I now I have an efficient way to

a) identify the invalid records:

SELECT [801_Table1].FIELD2 FROM 801_Table1 WHERE ((([801_Table1].FIELD2) Like "*" & " 24:" & "*"))

... and (with your solution) resolved the invalid text efficiently before executing the TRIM & CONVERSION routine.

SELECT [801_Table2].FIELD2, Format(DateValue(Left([FIELD2],10))+TimeSerial(Mid([FIELD2],12,2),Mid([FIELD2],15,2),Mid([FIELD2],18,2)),"yyyy/mm/dd hh:nn:ss:" & Mid([FIELD2],21,3)) AS DTMS_TXT
FROM 801_Table2;

Thank you and be blessed!

Tom

[accesstos]

Happy to help Tom!

Good luck with your project!

John