Re: HOW DO I ENTER 3.51.3 AS NUMBER IN ACCESS (TRAVELLING TIME)
david@epsomdotcomdotau
or
0.16045
(david)
"Stuck On Time" <Stuck On Ti...@discussions.microsoft.com> wrote in message
news:FAB17D15-A4A5-4AA4...@microsoft.com...
> 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
Douglas J. Steele
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.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
Stuck On Time
Thank You
Stuck On Time
dont have to do any calculations i should be okay
John Vinson
Ti...@discussions.microsoft.com> wrote:
>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
Douglas J. Steele wrote:
> the [DATETIME] data type is not
> intended to handle durations: it's meant for timestamps (i.e. specific
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
news:1158573397.0...@i3g2000cwc.googlegroups.com...
>
> Douglas J. Steele wrote:
>> 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.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
James A. Fortune
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
MPAP...@FortuneJames.com
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
MPAP...@FortuneJames.com
Jamie Collins
James A. Fortune wrote:
> 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.
--
david@epsomdotcomdotau
like 'average time', or reformat the values (3:51:03 instead of 3.51.3),
text values can be easier to work with.
(david)
"Stuck On Time" <Stuck...@discussions.microsoft.com> wrote in message
news:20DC583C-2CE3-411C...@microsoft.com...
david@epsomdotcomdotau
>
> 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)
"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1158586576.1...@h48g2000cwc.googlegroups.com...
John Vinson
>"If m immediately follows h or hh, the minute rather than the month
> is displayed."
<boggle>
>I didn't know that!
Me neither. Wow.
John W. Vinson[MVP]
Jamie Collins
John Vinson wrote:
> >"If m immediately follows h or hh, the minute rather than the month
> > is displayed."
>
> <boggle>
>
> >I didn't know that!
>
> Me neither. Wow.
Of all the things I've brought to the group, *this* has the 'wow'
factor <g>?!
Jamie.
--
James A. Fortune
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
MPAP...@FortuneJames.com
Playing the player rather than playing the table is known as the hustle.
Not very respectable. -- Jeff Macauley
Jamie Collins
James A. Fortune wrote:
> 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
<jamiec...@xsmail.com> wrote:
>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]