format a field in "mm:ss.000"

[Matt D Francis]

Hi,

I have data in an Excel sheet which I want to get into Access. One field is
formatted with a custom format of mm:ss.000

to show minutes, seconds and thousands of a second e.g

01:13.256

like a stop watch might.

How can I format the corresponding field in my Access table so that the data
will be treated the same way.

Currently I can store it as text, but I will want to do calculations like
averages on it.

I tried a custom format of

nn:ss.000

but i think with no luck I think it tried to just add three zeroes at the end

Cheers,

 

[John Vinson]

Hi,

I have data in an Excel sheet which I want to get into Access. One field is
formatted with a custom format of mm:ss.000

That format is unfortunately simply not available in Access. It *just
doesn't exist*.

Date/Time fields give one second precision, but no finer than that.

I'd suggest storing this duration data in either a Long Integer field
storing milliseconds, or a Double number field storing seconds and
fractions of a second. You can use an expression like

[Duration] \ 60 & ":" & Format([Duration] - 60*([Duration] \ 60),
"00.000")

to display 185.246 as 3:05.246. If you want to enter data into this
field you'll probably want two textboxes, one for the minutes and the
other for seconds, with some code to combine them.

John W. Vinson[MVP]

 

[Matt D Francis]

Ok well thank-you anyway, will give it a go the way you advised.

Many Thanks

Matt

Hi,

I have data in an Excel sheet which I want to get into Access. One field is
formatted with a custom format of mm:ss.000

That format is unfortunately simply not available in Access. It *just
doesn't exist*.

Date/Time fields give one second precision, but no finer than that.

I'd suggest storing this duration data in either a Long Integer field
storing milliseconds, or a Double number field storing seconds and
fractions of a second. You can use an expression like

[Duration] \ 60 & ":" & Format([Duration] - 60*([Duration] \ 60),
"00.000")

to display 185.246 as 3:05.246. If you want to enter data into this
field you'll probably want two textboxes, one for the minutes and the
other for seconds, with some code to combine them.

John W. Vinson[MVP]