Is it possible to have half seconds in access like it is in Excel? I assumed so but everytime I try to import a .csv file (they were Excel files a while ago but that's a long story) I get a type conversion error. I have been into the design view of the table where I'm importing the csv's and changed the format of the column recieveing the time/date information to "mm/dd/yy hh:mm:ss.0" (though the minutes get changed to "nn" for some reason). This format worked in Excel and I though it would to in Access. Any ideas on why I'm getting the type conversion error?
Table Date/Time Format
- Thread starter radon
- Start date
datAdrenaline
AWF VIP
- Local time
- Yesterday, 19:53
- Joined
- Jun 23, 2008
- Messages
- 687
>> Is it possible to have half seconds in access like it is in Excel? <<
Access's Date/Time data time is geared for a Moment in time, not an elapsed time.
Therefore, the resolution of Access's date/time data type is to the second.
So you get the error simply because when Access "sees" a partial second in the string that is being dumped to the field, Access will first try its best to make that string a valid date/time value, but alas it can not because of the partial seconds tagged to it, Access then has no choice but to categorized the incoming data as the Text datatype, which causes the error you are seeing since a Text value can not be written to a Date/Time data type.
Access's Date/Time data time is geared for a Moment in time, not an elapsed time.
Therefore, the resolution of Access's date/time data type is to the second.
So you get the error simply because when Access "sees" a partial second in the string that is being dumped to the field, Access will first try its best to make that string a valid date/time value, but alas it can not because of the partial seconds tagged to it, Access then has no choice but to categorized the incoming data as the Text datatype, which causes the error you are seeing since a Text value can not be written to a Date/Time data type.
datAdrenaline
AWF VIP
- Local time
- Yesterday, 19:53
- Joined
- Jun 23, 2008
- Messages
- 687
As a work around, you could store the string that has the format you wish in a Text data typed field. Then when you work with that value, you would need to create functions that can deal with that resolution.
May I ask what you are tracking that requires partial seconds? ... is it an elapsed time of sorts? ... are you calculating an elapsed time with it?
----
>> though the minutes get changed to "nn" for some reason <<
Just to let you know, VBA and access use the "n" to represent minutes so as to NOT conflict with the "m" for months. Access tries its best to "analyse" what you are asking for, and it makes the guess that you really meant "n" instead of "m" since the positioning of the "m" did not make sense, so Access changes the letters in order to avoid having to figure what you really meant each time the field is formatted for display ... (note: this is not be exact at the very low level, but that is how Access appears to behave
)
May I ask what you are tracking that requires partial seconds? ... is it an elapsed time of sorts? ... are you calculating an elapsed time with it?
----
>> though the minutes get changed to "nn" for some reason <<
Just to let you know, VBA and access use the "n" to represent minutes so as to NOT conflict with the "m" for months. Access tries its best to "analyse" what you are asking for, and it makes the guess that you really meant "n" instead of "m" since the positioning of the "m" did not make sense, so Access changes the letters in order to avoid having to figure what you really meant each time the field is formatted for display ... (note: this is not be exact at the very low level, but that is how Access appears to behave
)