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
|
|||
|
|||
Short Time Format Error
Hello All,
I am doing some work comparing phone calls on a bill against a telephone usage report. Thus I have two tables (Bill and Report) and I'm running a query comparing the date, time, call number, and length of call. However, a lot of the comparisons are failing and I've noticed it's because of the time. I have the fields the same for both tables (integer, text, etc.) as to avoid errors on the query. For time, I am using the Short Time format. The problem I have is that Access is storing some of them incorrectly. Allow me to explain. One of the tables is fine and shows 1:30pm as "13:30", the other table if you're just looking at it shows "13:30", but when you click on the field/record, it appears as "1:30:00 PM". Out of 180 records, only about 60 of them have this error and it's random in the table. Thus those 60 records are failing my query. Any help on how to fix this? Thanks in advance! |
#2
|
|||
|
|||
On Mon, 4 Apr 2005 14:23:04 -0700, "Ketan"
wrote: Hello All, I am doing some work comparing phone calls on a bill against a telephone usage report. Thus I have two tables (Bill and Report) and I'm running a query comparing the date, time, call number, and length of call. However, a lot of the comparisons are failing and I've noticed it's because of the time. I have the fields the same for both tables (integer, text, etc.) as to avoid errors on the query. For time, I am using the Short Time format. The problem I have is that Access is storing some of them incorrectly. Allow me to explain. One of the tables is fine and shows 1:30pm as "13:30", the other table if you're just looking at it shows "13:30", but when you click on the field/record, it appears as "1:30:00 PM". Out of 180 records, only about 60 of them have this error and it's random in the table. Thus those 60 records are failing my query. Any help on how to fix this? Thanks in advance! If you're using an Access Date/Time field for this value, the format of the field is *absolutely irrelevant*. Date/Time values are stored internally as a Double Float number, a count of days and fractions of a day (times) since midnight, December 30, 1899. The format of the field is *not* part of the stored value; it merely controls how the number is displayed. Try setting the format of the field in the table to Long Time (don't worry, this won't change the contents of any field). Are any of the values off by a second or so? A field might contain the actual numeric value 0.5625 which would *display* as 01:30pm, or as 13:30, or as #12/30/1899 13:30:00# or any of a variety of other appearances; another record might contain 0.562511574074074 (one second later), which would DISPLAY as 13:30 (since forcing the hh:nn format will truncate the remaining second) but not be equal. A Long Time would show it as 13:30:01. Depending on how the data was entered into the field, you might even have two values which differ by less than a half-second; since Access doesn't have provision for sub-second display accuracy, these values could be different yet look the same. John W. Vinson[MVP] |
#3
|
|||
|
|||
"John Vinson" wrote:
If you're using an Access Date/Time field for this value, the format of the field is *absolutely irrelevant*. Date/Time values are stored internally as a Double Float number, a count of days and fractions of a day (times) since midnight, December 30, 1899. The format of the field is *not* part of the stored value; it merely controls how the number is displayed. Try setting the format of the field in the table to Long Time (don't worry, this won't change the contents of any field). Are any of the values off by a second or so? A field might contain the actual numeric value 0.5625 which would *display* as 01:30pm, or as 13:30, or as #12/30/1899 13:30:00# or any of a variety of other appearances; another record might contain 0.562511574074074 (one second later), which would DISPLAY as 13:30 (since forcing the hh:nn format will truncate the remaining second) but not be equal. A Long Time would show it as 13:30:01. Depending on how the data was entered into the field, you might even have two values which differ by less than a half-second; since Access doesn't have provision for sub-second display accuracy, these values could be different yet look the same. John W. Vinson[MVP] The data was an import from an Excel spreadsheet. The format in the Excel sheet was long time and all had 0 seconds, so they read "11:47:00 AM" or "3:25:00 PM" I actually just scrolled through them again to make sure. I assume it's some kind of import error then on Access' end, but I don't know for sure since it's only happening to some of the records and not all. Also, is there a way to make Access force a format on the data? Like if say I have 2 values "11:47:00 AM" and "11:47:05 AM". Is there a way I can make them both into "11:47" as short time and have "11:47" be the new value of the record....this way I don't have any issues of the initial data being slightly different? Thanks again for your help. |
#4
|
|||
|
|||
On Tue, 5 Apr 2005 10:55:04 -0700, "Ketan"
wrote: The data was an import from an Excel spreadsheet. The format in the Excel sheet was long time and all had 0 seconds, so they read "11:47:00 AM" or "3:25:00 PM" I actually just scrolled through them again to make sure. I assume it's some kind of import error then on Access' end, but I don't know for sure since it's only happening to some of the records and not all. Just doublechecking: if you look at the table in design view, is this field Date/Time? or is it Text? Also, is there a way to make Access force a format on the data? Like if say I have 2 values "11:47:00 AM" and "11:47:05 AM". Is there a way I can make them both into "11:47" as short time and have "11:47" be the new value of the record....this way I don't have any issues of the initial data being slightly different? Thanks again for your help. Again, *DON'T* confuse the field *content* with the field *format*. You can set the Format property of a field to hh:nn, but that will not change the contents in the least! What you may want to do, just in case, is use an Update query squeezing the value through a call to the Format() *function* to discard sub-minute data: update the field to CDate(Format([fieldname], "hh:nn am/pm")) This will take a value of 11:47:05 and convert it to a text string "11:47 am"; CDate() will convert this back to a Date/Time and store the truncated value back into the table. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
file extension///Run Time error 2114 Doesn't support the format of | Dale | General Discussion | 0 | November 24th, 2004 07:23 PM |
Use first record found in expression? | CASJAS | Running & Setting Up Queries | 17 | July 22nd, 2004 09:21 PM |
Outlook Express Run Time error | Dudi | Outlook Express | 9 | June 20th, 2004 05:45 PM |
Outlook 2003 Terminal Server Time Zone issue | Robert Strom | Calendar | 2 | May 26th, 2004 10:50 PM |