If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to code a missing value in a Time field?
Any suggestions for a good "missing value" code in a Time field in Access?
I am working with a large dataset with start and end times for events of interest. In the original data, these times were entered as 3 or 4 digit numbers corresponding to the military time of the event of interest (e.g. 939, 1015, 1355, 2100) but not formatted as a time in Access. In this original system, missing values were coded as either 999 or 9999 to account for the fact that we know there is not a value for the time in that instance (rather than leaving it blank). I am trying to convert these start and end times into actual "Time" format so that they are more meaningful (e.g. 9:39, 10:15, etc.), however, I've run into a problem dealing with the "missing values" as Access will not allow an invalid time (e.g. 9:99 or 99:99) and we do not want to leave the field blank. Any ideas would be greatly appreciated! |
#2
|
|||
|
|||
How to code a missing value in a Time field?
You have two choices. Leave the time Null (since you don't know what it
should be), or set it to a predefined value. If you want to leave the time Null, you should be able to use an Update query along the lines of: UPDATE MyTable SET NewTimeField = CDate(Format(Right("0" & OldTimeField, 4), "00\:00")) WHERE TextTimeField NOT IN (999, 9999) If you want to set it to a predefined value, try something like: UPDATE MyTable SET NewTimeField = IIf(OldTimeField IN (999, 9999), #12:00:00#, CDate(Format(Right("0" & OldTimeField, 4), "00\:00"))) Incidentally, I'd question whether you should be storing time by itself. Access doesn't really support Time-only values: there's no Time data type, only a Date data type. The Date data type is an 8 byte floating point number where the integer portion represents the date as the number of days relative to 30 Dec, 1899, and the decimal portion represents the time as a fraction of a day. That means that if all you're storing is a time, to Access it's actually that time on 30 Dec, 1899. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Katie" wrote in message ... Any suggestions for a good "missing value" code in a Time field in Access? I am working with a large dataset with start and end times for events of interest. In the original data, these times were entered as 3 or 4 digit numbers corresponding to the military time of the event of interest (e.g. 939, 1015, 1355, 2100) but not formatted as a time in Access. In this original system, missing values were coded as either 999 or 9999 to account for the fact that we know there is not a value for the time in that instance (rather than leaving it blank). I am trying to convert these start and end times into actual "Time" format so that they are more meaningful (e.g. 9:39, 10:15, etc.), however, I've run into a problem dealing with the "missing values" as Access will not allow an invalid time (e.g. 9:99 or 99:99) and we do not want to leave the field blank. Any ideas would be greatly appreciated! |
#3
|
|||
|
|||
How to code a missing value in a Time field?
If you want to convert the values purely for presentation purposes you can
keep the existing values and format them with: Format(YourTimeField,"0:00") If you want to return it as an actual date/time value for doing computations on the values, e.g. the difference between two times you can return a date/time value with: CDate(Format(YourTimeField,"0:00")) Note that this would raise an error with values like 9999 or 999 which would need to be handled. Bear in mind that, as Doug said, there is no such thing as a Time value in Access, nor indeed a Date value, only a DateTime value. You can see this with your data with the following expression: Format(Format(YourTimeField,"0:00"),"dd mmmm yyyy hh:nn:ss") Note that its not necessary to use the CDate function when doing this. You'll see that this will return a time on 30 December 1899, which is day-zero in Access's implementation of the DateTime data type. If you doing something like subtracting one time from another to get the time difference then the date is irrelevant of course, but if you then do something like adding the resulting time differences together to get the sum of the durations of a number of events you'll get some strange results if the total duration is 24 hours or more. There are ways around this, however. Ken Sheridan Stafford, England "Katie" wrote: Any suggestions for a good "missing value" code in a Time field in Access? I am working with a large dataset with start and end times for events of interest. In the original data, these times were entered as 3 or 4 digit numbers corresponding to the military time of the event of interest (e.g. 939, 1015, 1355, 2100) but not formatted as a time in Access. In this original system, missing values were coded as either 999 or 9999 to account for the fact that we know there is not a value for the time in that instance (rather than leaving it blank). I am trying to convert these start and end times into actual "Time" format so that they are more meaningful (e.g. 9:39, 10:15, etc.), however, I've run into a problem dealing with the "missing values" as Access will not allow an invalid time (e.g. 9:99 or 99:99) and we do not want to leave the field blank. Any ideas would be greatly appreciated! |
Thread Tools | |
Display Modes | |
|
|