Use SELECT CASE statement in UPDATE query in VB Module, to convert Open Access Time
Hi people,
I'm working on upgrading an old DOS Open Access database for a carpet cleaning company. All the fields were imported into MS Access fine, EXCEPT the time field. This was imported as a Long Integer number.
I've now found out how to convert the long integer into a valid number format in MS Access and have created a SELECT CASE code matching up the common long integer values with their equivalent time value. In other words, a TIME MATRIX!!
However, I don't know how to use this code to update my tables. I've thought I could use an UPDATE query, but I don't know how to implement the SELECT CASE statement into the query to provide the results I want.
I've tried to create a Function I can run in the Immediate window to update the table with the new values of Time. No luck so far.
Anyone had this problem before? You're welcome to use my Time Matrix if it helps.
Anyway, here's the code:
Listing 1.1
=========================================================
Dim MyTime As Long
Select Case MyTime 'Update Time number to normal time
Case 0
MyTime = #12:00:00 AM#
Case 5460
MyTime = #1:15:00 AM#
Case 9120
MyTime = #2:15:00 AM#
Case 12780
MyTime = #3:30:00 AM#
Case 21960
MyTime = #6:00:00 AM#
Case 25620
MyTime = #7:00:00 AM#
Case 26520
MyTime = #7:15:00 AM#
Case 27420
MyTime = #7:30:00 AM#
Case 28320
MyTime = #7:45:00 AM#
Case 28620
MyTime = #7:50:00 AM#
Case 29280
MyTime = #8:00:00 AM#
Case 30180
MyTime = #8:15:00 AM#
Case 31080
MyTime = #8:30:00 AM#
Case 31980
MyTime = #8:45:00 AM#
Case 32940
MyTime = #9:00:00 AM#
Case 33840
MyTime = #9:15:00 AM#
Case 34140
MyTime = #9:20:00 AM#
Case 34740
MyTime = #9:30:00 AM#
Case 35640
MyTime = #9:45:00 AM#
Case 35940
MyTime = #9:50:00 AM#
Case 36600
MyTime = #10:00:00 AM#
Case 36900
MyTime = #10:05:00 AM#
Case 37500
MyTime = #10:15:00 AM#
Case 38400
MyTime = #10:30:00 AM#
Case 39300
MyTime = #10:45:00 AM#
Case 40260
MyTime = #11:00:00 AM#
Case 41160
MyTime = #11:15:00 AM#
Case 41460
MyTime = #11:20:00 AM#
Case 42060
MyTime = #11:30:00 AM#
Case 42660
MyTime = #11:40:00 AM#
Case 42960
MyTime = #11:45:00 AM#
Case 43260
MyTime = #11:50:00 AM#
Case 43560
MyTime = #11:55:00 AM#
Case 43800
MyTime = #11:59:00 AM#
Case 43920
MyTime = #12:00:00 PM#
Case 44520
MyTime = #12:10:00 PM#
Case 44820
MyTime = #12:15:00 PM#
Case 45720
MyTime = #12:30:00 PM#
Case 46620
MyTime = #12:45:00 PM#
Case 47580
MyTime = #1:00:00 PM#
Case 48480
MyTime = #1:15:00 PM#
Case 48780
MyTime = #1:20:00 PM#
Case 49380
MyTime = #1:30:00 PM#
Case 49500
MyTime = #1:35:00 PM#
Case 49680
MyTime = #1:40:00 PM#
Case 50280
MyTime = #1:45:00 PM#
Case 50580
MyTime = #1:50:00 PM#
Case 51240
MyTime = #2:00:00 PM#
Case 52140
MyTime = #2:15:00 PM#
Case 52440
MyTime = #2:20:00 PM#
Case 53040
MyTime = #2:30:00 PM#
Case 53940
MyTime = #2:45:00 PM#
Case 54900
MyTime = #3:00:00 PM#
Case 55200
MyTime = #3:05:00 PM#
Case 55500
MyTime = #3:10:00 PM#
Case 55800
MyTime = #3:15:00 PM#
Case 56700
MyTime = #3:30:00 PM#
Case 57000
MyTime = #3:35:00 PM#
Case 57600
MyTime = #3:45:00 PM#
Case 58560
MyTime = #4:00:00 PM#
Case 59160
MyTime = #4:10:00 PM#
Case 59460
MyTime = #4:15:00 PM#
Case 60060
MyTime = #4:25:00 PM#
Case 60360
MyTime = #4:30:00 PM#
Case 61260
MyTime = #4:45:00 PM#
Case 61560
MyTime = #4:59:00 PM#
Case 62220
MyTime = #5:00:00 PM#
Case 62820
MyTime = #5:10:00 PM#
Case 63120
MyTime = #5:15:00 PM#
Case 63420
MyTime = #5:20:00 PM#
Case 64020
MyTime = #5:30:00 PM#
Case 65880
MyTime = #6:00:00 PM#
Case 66780
MyTime = #6:15:00 PM#
Case 69540
MyTime = #7:00:00 PM#
Case 76860
MyTime = #9:15:00 PM#
Case 80520
MyTime = #10:15:00 PM#
Case 82320
MyTime = #10:45:00 PM#
Case 84180
MyTime = #11:15:00 PM#
Case 85980
MyTime = #11:45:00 PM#
'Case Else
End Select
=========================================================
Hi people,
I'm working on upgrading an old DOS Open Access database for a carpet cleaning company. All the fields were imported into MS Access fine, EXCEPT the time field. This was imported as a Long Integer number.
I've now found out how to convert the long integer into a valid number format in MS Access and have created a SELECT CASE code matching up the common long integer values with their equivalent time value. In other words, a TIME MATRIX!!
However, I don't know how to use this code to update my tables. I've thought I could use an UPDATE query, but I don't know how to implement the SELECT CASE statement into the query to provide the results I want.
I've tried to create a Function I can run in the Immediate window to update the table with the new values of Time. No luck so far.
Anyone had this problem before? You're welcome to use my Time Matrix if it helps.
Anyway, here's the code:
Listing 1.1
=========================================================
Dim MyTime As Long
Select Case MyTime 'Update Time number to normal time
Case 0
MyTime = #12:00:00 AM#
Case 5460
MyTime = #1:15:00 AM#
Case 9120
MyTime = #2:15:00 AM#
Case 12780
MyTime = #3:30:00 AM#
Case 21960
MyTime = #6:00:00 AM#
Case 25620
MyTime = #7:00:00 AM#
Case 26520
MyTime = #7:15:00 AM#
Case 27420
MyTime = #7:30:00 AM#
Case 28320
MyTime = #7:45:00 AM#
Case 28620
MyTime = #7:50:00 AM#
Case 29280
MyTime = #8:00:00 AM#
Case 30180
MyTime = #8:15:00 AM#
Case 31080
MyTime = #8:30:00 AM#
Case 31980
MyTime = #8:45:00 AM#
Case 32940
MyTime = #9:00:00 AM#
Case 33840
MyTime = #9:15:00 AM#
Case 34140
MyTime = #9:20:00 AM#
Case 34740
MyTime = #9:30:00 AM#
Case 35640
MyTime = #9:45:00 AM#
Case 35940
MyTime = #9:50:00 AM#
Case 36600
MyTime = #10:00:00 AM#
Case 36900
MyTime = #10:05:00 AM#
Case 37500
MyTime = #10:15:00 AM#
Case 38400
MyTime = #10:30:00 AM#
Case 39300
MyTime = #10:45:00 AM#
Case 40260
MyTime = #11:00:00 AM#
Case 41160
MyTime = #11:15:00 AM#
Case 41460
MyTime = #11:20:00 AM#
Case 42060
MyTime = #11:30:00 AM#
Case 42660
MyTime = #11:40:00 AM#
Case 42960
MyTime = #11:45:00 AM#
Case 43260
MyTime = #11:50:00 AM#
Case 43560
MyTime = #11:55:00 AM#
Case 43800
MyTime = #11:59:00 AM#
Case 43920
MyTime = #12:00:00 PM#
Case 44520
MyTime = #12:10:00 PM#
Case 44820
MyTime = #12:15:00 PM#
Case 45720
MyTime = #12:30:00 PM#
Case 46620
MyTime = #12:45:00 PM#
Case 47580
MyTime = #1:00:00 PM#
Case 48480
MyTime = #1:15:00 PM#
Case 48780
MyTime = #1:20:00 PM#
Case 49380
MyTime = #1:30:00 PM#
Case 49500
MyTime = #1:35:00 PM#
Case 49680
MyTime = #1:40:00 PM#
Case 50280
MyTime = #1:45:00 PM#
Case 50580
MyTime = #1:50:00 PM#
Case 51240
MyTime = #2:00:00 PM#
Case 52140
MyTime = #2:15:00 PM#
Case 52440
MyTime = #2:20:00 PM#
Case 53040
MyTime = #2:30:00 PM#
Case 53940
MyTime = #2:45:00 PM#
Case 54900
MyTime = #3:00:00 PM#
Case 55200
MyTime = #3:05:00 PM#
Case 55500
MyTime = #3:10:00 PM#
Case 55800
MyTime = #3:15:00 PM#
Case 56700
MyTime = #3:30:00 PM#
Case 57000
MyTime = #3:35:00 PM#
Case 57600
MyTime = #3:45:00 PM#
Case 58560
MyTime = #4:00:00 PM#
Case 59160
MyTime = #4:10:00 PM#
Case 59460
MyTime = #4:15:00 PM#
Case 60060
MyTime = #4:25:00 PM#
Case 60360
MyTime = #4:30:00 PM#
Case 61260
MyTime = #4:45:00 PM#
Case 61560
MyTime = #4:59:00 PM#
Case 62220
MyTime = #5:00:00 PM#
Case 62820
MyTime = #5:10:00 PM#
Case 63120
MyTime = #5:15:00 PM#
Case 63420
MyTime = #5:20:00 PM#
Case 64020
MyTime = #5:30:00 PM#
Case 65880
MyTime = #6:00:00 PM#
Case 66780
MyTime = #6:15:00 PM#
Case 69540
MyTime = #7:00:00 PM#
Case 76860
MyTime = #9:15:00 PM#
Case 80520
MyTime = #10:15:00 PM#
Case 82320
MyTime = #10:45:00 PM#
Case 84180
MyTime = #11:15:00 PM#
Case 85980
MyTime = #11:45:00 PM#
'Case Else
End Select
=========================================================
Last edited: