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

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

 

[Uncle Gizmo]

Converts open access time to MS access time

That is a very nice case statement you have got there!

I have used your case statement in an example database attached.

I'm not sure if it will work correctly because I am not sure what will happen when you put the data into an MS Access time date field.


I would suggest that if you post more information about why the time in the old database is in that particular format, then the conversion utility may prove useful for other applications.

 

[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.

 

[Uncle Gizmo]

>>> which I call "Time" field <<<<

Time is an MS Access reserved word, along with date, and some other words. So you may run into problems if you name a field "time", "date" or any other reserve word.

There are several naming conventions, so in your general code it is quite easy to avoid using reserved words because if you had a string variable then you would most likely call it something like: "strTime" thus avoiding a reserved word conflict.

So as a general rule, if you use any "real" word it is a good idea to customize the word, to your own programming style, or by adding a naming convention prefix, alternatively you can add your own prefix, sometimes I use my initials.

It is possible that Microsoft may and more reserved words in the future. So if you adopt a naming convention, it does protect your code for future use, possibly for use in other applications.

 

[Uncle Gizmo]

>>> So my problem now is: <<<

I think you should be able to follow from the attached database that demonstrates how to convert the "long" time format into the time format you provide in your case statement.

This uses an update query to update the second column in the sample table provided.

I indicated there may be some problems with this sample, because when I first ran it, I saved the results into a time/date field, but the format didn't look correct. So I changed the field to a text type field and Convert and worked out OK. I then change the text field back into a time/date field, and LO and behold it worked OK then.

I have noted this type of thing with a MS Access before, where sometimes it does not work as you expect, you change from one data type to another, and it works OK, and then you change back to the original data type and for some strange reason it still works OK. It's as if MS Access has some sort of memory, or somehow learns what you want!

 

[Uncle Gizmo]

>>> approximately the number of seconds after midnight <<<<

You may find it beneficial to replace the case statement with a routine that uses the above information to actually calculate each new time and convert in this manner. If you did this, the function would then be able to cater for any time that it was presented with.

 

[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

 

[Pat Hartman]

I don't have a dBase file handy to try this but see if you can make an import spec. If you get the import wizard when you import a dBase file, you can tell Access that a particular field is a date/time field and specify its format so that Access imports it properly.

Something you need to be aware of is that Access does NOT store date/time values a strings. They are stored as double precision numbers with the integer portion representing the number of days since Dec 30, 1899 and the decimal value representing the elapsed time since midnight. You may format a date/time however you chose but that doesn't impact how it is stored.

 

[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.