HOW DO I ENTER 3.51.3 AS NUMBER IN ACCESS (TRAVELLING TIME)

[Guest]

i am wanting to enter hours minutes seconds as number in a data column this
being the time taken to cover a specifc distance. Putting the second decimal
point in throughs it out. Any Help appreciated

 

[Guest]

3:51:3

or
0.16045

(david)

 

[Douglas J. Steele]

What data type are you using? I just did a test using a Date field, and it
accepted 8.30.15 without complaint.

There are a couple of points to mention, though. Access uses whatever time
separator has been defined to the operating system through Regional
Settings. Go into the Control Panel and find the applet for "Regional And
Language Options". Start it up, and click on the "Customize..." button on
the Regional Options tab. Go to the Time tab, and check what's set for the
Time separator.

Second, and far more important, is the fact that the Date data type is not
intended to handle durations: it's meant for timestamps (i.e. specific
points in time). This is because of how Date values are stored: they're 8
byte floating point numbers, 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. Especially if you're going to
try and add the durations together, you're going to be disappointed, because
it's going to treat 24 hours as a day, and you'll lose that part of the
total when it displays the total. What's recommended for durations is to
store the values as Long Integers, where 1 represents the smallest
resolution you require (1 second in your case). 8.30.15 would be entered as
30615 (8*3600 + 30*60 + 15). From the point of view of data entry, have an
unbound text box on your form. In the AfterUpdate event of that text box,
use a function to convert the h.mm.ss input to total seconds, and store that
in the table. Create your own function to convert total seconds to h.mm.ss
for display purposes.

Post back if you have further questions.

 

[Guest]

Once i changed the format to text it took 3.51.3 as well as your suggestion
Thank You

 

[Guest]

Thank you for suggestion I have entered it as text not numbers as long as i
dont have to do any calculations i should be okay

 

[John Vinson]

On Sun, 17 Sep 2006 01:15:01 -0700, Stuck On Time <Stuck On

i am wanting to enter hours minutes seconds as number in a data column this
being the time taken to cover a specifc distance. Putting the second decimal
point in throughs it out. Any Help appreciated

I'd store the value in integer seconds.

You can use three unbound textboxes on a Form, for hours, minutes, and
seconds respectively; and a fourth bound taxtbox for the time value.
In the AfterUpdate event of each unbound textbox, "push" the current
travel time into the bound column with code like

Private Sub txtSeconds_AfterUpdate()
If Not (IsNull(Me!txtSeconds) Or IsNull(Me!txtMinutes) _
Or IsNull(Me!txtHours)) Then
Me!txtTravelTime = Me!txtHours*3600 + Me!txtMinutes * 60 _
+ Me!txtSeconds
End If
End Sub

In the Form's Current event, parse out the hours, minutes and seconds
into the three textboxes.

John W. Vinson[MVP]

 

[Jamie Collins]

the [DATETIME] data type is not
intended to handle durations: it's meant for timestamps (i.e. specific
points in time). This is because of how [DATETIME] values are stored

No, it has nothing to do with the way the SQL implementation persists
the values e.g. Access/Jet could store DATETIME values as text (as does
one SQL product I have used) and it would make no difference. You got
it right in your first (quoted) sentence i.e. Access/Jet's DATETIME is
synonymous with the TIMESTAMP data type in standard SQL.

Standard SQL has an INTERVAL data type but it has not implemented in
Access/Jet, nor any other SQL product AFAIK. Instead, intervals are
modelled in SQL using pairs of DATETIME (TIMESTAMP) values to model
start and end respectively. Google for the work of Snodgrass on
temporal data in SQL.

Jamie.

--

 

[Douglas J. Steele]

the [DATETIME] data type is not
intended to handle durations: it's meant for timestamps (i.e. specific
points in time). This is because of how [DATETIME] values are stored

No, it has nothing to do with the way the SQL implementation persists
the values e.g. Access/Jet could store DATETIME values as text (as does
one SQL product I have used) and it would make no difference. You got
it right in your first (quoted) sentence i.e. Access/Jet's DATETIME is
synonymous with the TIMESTAMP data type in standard SQL.

Poor wording on my part. I meant the explanation of how the data was stored
to illustrate why storing durations leads to problems, not to imply that you
couldn't store durations because of how it was stored.

 

[James A. Fortune]

the [DATETIME] data type is not
intended to handle durations: it's meant for timestamps (i.e. specific
points in time). This is because of how [DATETIME] values are stored

No, it has nothing to do with the way the SQL implementation persists
the values e.g. Access/Jet could store DATETIME values as text (as does
one SQL product I have used) and it would make no difference. You got
it right in your first (quoted) sentence i.e. Access/Jet's DATETIME is
synonymous with the TIMESTAMP data type in standard SQL.

Standard SQL has an INTERVAL data type but it has not implemented in
Access/Jet, nor any other SQL product AFAIK. Instead, intervals are
modelled in SQL using pairs of DATETIME (TIMESTAMP) values to model
start and end respectively. Google for the work of Snodgrass on
temporal data in SQL.

Jamie.

Jamie,

Should I believe a guy who thought the format for minutes in Access uses
m rather than n ? Seriously, the pdf link you posted a while back
for Snodgrass was quite interesting and will definitely be involved in
my future plans for dealing with temporal data. I also appreciate the
insights you shared recently about CHECK constraints. Google produced
too much information on Snodgrass and temporal databases. What specific
links would be a good next step for an Access developer who has read the
Snodgrass book?

James A. Fortune
(e-mail address removed)

Access Tip:

Some excellent Access performance tips can be found here:

MSDN Book Excerpts
Chapter 15: Application Optimization
Access 2002 Desktop Developer's Handbook, Paul Litwin, Ken Getz, and
Mike Gunderloy
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/odc_4013c03.asp

 

[James A. Fortune]

Access Tip:

Some excellent Access performance tips can be found here:

MSDN Book Excerpts
Chapter 15: Application Optimization
Access 2002 Desktop Developer's Handbook, Paul Litwin, Ken Getz, and
Mike Gunderloy
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/odc_4013c03.asp

I just tested the link I provided in the Access Tip. The excerpt I
cited is the one below that one. That excerpt doesn't have a distinct URL.

James A. Fortune
(e-mail address removed)

 

[Jamie Collins]

Should I believe a guy who thought the format for minutes in Access uses
m rather than n ?

But it *is* m e.g.

SELECT FORMAT(#2001-01-01 23:59:59#, 'h:m')

returns '23:59' rather than '23:1'. OK so that doesn't *always* hold
true...

Seriously, the pdf link you posted a while back
for Snodgrass was quite interesting and will definitely be involved in
my future plans for dealing with temporal data.

What specific
links would be a good next step for an Access developer who has read the
Snodgrass book?

As regards temporal data, reading the Snodgrass book is all I ask of
every Access user <g>. Seriously, IMO the book is well advanced of what
the Access/Jet engine can currently do i.e. I'm not sure there is any
more info out there (not that I've looked too hard).

Jamie.

--

 

[Guest]

It depends what you want. Unless you want to do calculations,
like 'average time', or reformat the values (3:51:03 instead of 3.51.3),
text values can be easier to work with.

(david)

 

[Guest]

SELECT FORMAT(#2001-01-01 23:59:59#, 'h:m')

returns '23:59' rather than '23:1'. OK so that doesn't *always* hold

"If m immediately follows h or hh, the minute rather than the month
is displayed."

I didn't know that! And not only that, I see that it has always
been that way - at least it's in the Access 2.0 help file.

(david)

 

[John Vinson]

"If m immediately follows h or hh, the minute rather than the month
is displayed."

I didn't know that!

Me neither. Wow.

John W. Vinson[MVP]

 

[Jamie Collins]

Me neither. Wow.

Of all the things I've brought to the group, *this* has the 'wow'
factor <g>?!

Jamie.

--

 

[James A. Fortune]

Of all the things I've brought to the group, *this* has the 'wow'
factor <g>?!

Jamie.

This is nothing other than the irony factor at work. The most profound
thing I ever said in any newsgroup got rated a single star. You need to
watch out for circumstances in life where irony has a chance to
function. I said 'wow' also. Half of the reason was, like most, I
didn't notice it in the help file. The other half was that I couldn't
believe that Microsoft would do something so bizarre and cobbled in an
attempt to coddle the user experience. How can you rely on something
that changes depending on the situation?

James A. Fortune
(e-mail address removed)

Playing the player rather than playing the table is known as the hustle.
Not very respectable. -- Jeff Macauley

 

[Jamie Collins]

I couldn't
believe that Microsoft would do something so bizarre and cobbled in an
attempt to coddle the user experience. How can you rely on something
that changes depending on the situation?

Such behaviour by design is not unique in the product. The most
bizarre, IMO, is the INSTR() function, where the first argument is
optional but the next two arguments are not i.e.

InStr([start, ]string1, string2[, compare])

So, in this example:

SELECT INSTR(1, 1, 1)

is it start=1 or compare=vbTextCompare?!

Jamie.

--

 

[John Vinson]

Of all the things I've brought to the group, *this* has the 'wow'
factor <g>?!

Wow as in "that's truly wierd" as opposed to "that's really neat"...
which is a more typical reaction to your erudite responses!

John W. Vinson[MVP]