Use SELECT CASE statement in UPDATE query in VBA

[Curious]

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

=========================================================

 

[Curious]

Connect to Table and UPDATE Time Field with Time Matrix Function

Thanks for cleaning up my function code Uncle Gizmo.

In Open Access, the Time value is shown as normal, e.g. 12:00 AM. Let me call this "OpenAccessTime".

However, when the OpenAccessTime field is imported into MS Access, the time field's data type becomes a Long Integer, and as you would expect, the value stored in the field are also long integers. I have called the field in the MS Access table "TimeOld".

After speaking with some techies in an Open Access Forum, I found out that the long int number is approximately the number of seconds after midnight. Using that as a basis for the calculation, I was able to come up with the Time Matrix Function listed previously (Listing 1.1). Which, you'll notice if you inspect carefully, you can see the long int numbers I am getting as time values.

I have now creating another Time field in my MS Access table, with a Date/Time data type, which I call "Time" field. So I've got both the long int values stored in the field "TimeOld" and 85 000 blank records for the adjacent "Time" field. I want to use my Time Matrix Function to fill in the blank "Time" field records with their corresponding time value in the "TimeOld" field.

So my problem now is:
1. Making a connection with my table, called "Jobs".
2. Using the Time Matrix function to UPDATE the field "Time" with the correct time value according to the long int stored in the adjacent field "newTime".


Hmmm.. I hope I haven't confused anyone yet. It's a mouthful. I hope that makes more sense.

 

[Curious]

Update Query Worked Successfully - Thanks!

Uncle Gizmo your a GENIUS!!!

I didn't realise you could use a function in an Update Query. It makes a lot more sense to me to do it that way. I find it easier to understand.

I hadn't noticed the Update query and the test table data you had provided in the previous database attachment, until now. I used your update query on the newTime field, and as you mentioned, run into a few problems because of the Date/Time data type. When I changed the newTime field to a text data type, no errors occurred.

However, I changed the SELECT CASE module slightly to overcome the runtime errors. When I changed the CASE ELSE fConvToTime = "" instead of CASE ELSE fConvToTime = "????", no more errors occured even when the newTime field having a Date/Time data type.

Unfortunately, the idea that the long int value is = the number of seconds after midnight, is a good approximation, but in order to get better approximations I had to minus 0.15. I don't know why this got us better approx. values.

Conversion Formula = (oldTime / 3600) - 0.15 = good time approximation

For this reason, I came up with the Time Matrix to say what 'normal' time the approx. time was supposed to be.

Anyway, after running your query on my table of 85 000 job records, only 241 values were not converted successfully. I found 71 unique long int time values that were not in the Time Matrix.

This is quite a manageable number to manually update.

Thanks again for your advice Unlce Gizmo.

I hope this information is helpful to others who may have similar problems converting time values from Open Access to Access or other db programs.

 

[Curious]

Calculation to Convert Open Access database time fields to dBase3 format

Well, after all my messing around, I found out what some of the problems were with the calculation.

I found the Open Access Users Group has encounted this problem before and they have written an article about the calculation to use to convert the time correctly each time.

Apparantely, as dBase3 format doesn't have a explicit Time field data type, Open Access converts it's time values into a long int value that is supposed to be equal to the number of seconds after midnight.

However, the "algorithm adds one minute for every hour". So you need to change the formula slightly to get the right results.

S = A - 60*INT(A/3660)

Where:

S = converted time to dBase3 format (MS Access format)

A = the origianl time value produced by Open Access

Here's the link if you want to read more about it or potentional errors: http://www4.tpg.com.au/users/aoaug/oauoct95.txt

 

[Curious]

The VB Function for Converting OpenAccess Time to MS Access Time

Well I'm certainly learning heaps about how time is stored in databases since doing this database project.

After all the messing around, I've finally come up with a simple Function that takes the OpenAccess long int value that is created when you try to import the file directly into dBase3 format, and converts it to the appropriate time in MS Access.

I now realise the TIME MATRIX I listed previosly (Listing 1.1) is incorrect. Not by much, but by about 15 minutes in each conversion.

This function works just fine:


Listing 2.0
===========================================================
'Convert OpenAccessTime into a valid time format in MS Access
'OpenAccessTime As Long is imported into function from dBase3 file

Public Function fConvToTime (lngOpenAccessTime As Long) As String

Dim lngCorrectedOpenAccessTime As Long
Dim lngHours As Long
Dim lngMinutes As Long
Dim lngSeconds As Long

lngCorrectedOpenAccessTime = lngOpenAccessTime - 60 * Int(lngOpenAccessTime / 3660)
lngHours = lngCorrectedOpenAccessTime / 3600
lngMinutes = (lngCorrectedOpenAccessTime / 60) - (60 * lngHours)
lngSeconds = lngCorrectedOpenAccessTime - ((3600 * lngHours) + (60 * lngMinutes))
fConvToTime = TimeSerial(lngHours, lngMinutes, lngSeconds)

End Function
==========================================================

I ran this function in an UPDATE query and created a new field called "Time" in my table, with a Date/Time data type.

This function converted everything perfectly without any problems. That little 10+ line code is going to save me literally hours and hours of work.

Thankyou to those who helped.