Converting string fields to time and subtracting

  • Thread starter Thread starter Anamik
  • Start date Start date
A

Anamik

Hi,

I am new to Access and my first task is to convert two string fields to time
and subtract them. I have been given a view containing the strings from a
database. First thing, I do not understand how I can add a new column to
this view, at least I don't see it in the menu, it is deactivated. Do I have
to add a new query? Next problem is how to convert the text fields to time
when subtracting them? Does it allow negative values in the time field? It
will be very helpful even if you can help with the conversion of the fields
and a pointer where I can read about the basic things. Thank you very much
in advance.

Anamik.
 
Anamik, there's quite a bit of reading to be done about dates and
times in Help because it is beyond the scope here to get it all down.
There are a number of ways to convert to dates, some are mentioned in
Help under Date Functions
cDate() converts a date
#12/30/2004# changes text to a date too
There's lot's more. Here's an overview about understanding dates that
I hope will help you.

------------ About Dates & Times in Access ------------------------

Understanding what "time" is in Access helps. Times and Dates in
Access are in fact simply a number. The Integer portion is the number
of days since Microsoft deemed that the world begun at 12 midnight 31
December 1899. The time is the decimal portion of the number. So at
its base level, all Access is dealing with is this number, it just
then formats it in all different ways for us to understand.

It's important to know the differences between the functions Date()
and Now(). Date() records just the number of days (as an integer)
since 31 Dec 1899 and displays it as a date. Now() records the days
and decimal part of the day since 31 Dec 1899.

If you open the Immediate Pane ALT-G, you can follow this yourself and
also try some variations. Here's an example, this is what the dates
and times really look like...

? Date()
28/07/04 = Todays date

? Format(Date(),"#,000.0000000000")
38,196.0000000000 = How access actually stores,
reads and uses it. The number of days since 31 Dec 1899.

So then, what is the date stamp at the same time?
? Now()
28/07/04 14:07:36

? Format(Now(),"#,000.0000000000")
38,196.5885995370 = You can see there is a
decimal part now which is the decimal portion of the day.

Lets just check that we have that right
? format(.5885995370, "Long Time")
14:07:35
------------------ (c) Brett Collings 2004 -------------------------




Hi,

I am new to Access and my first task is to convert two string fields to time
and subtract them. I have been given a view containing the strings from a
database. First thing, I do not understand how I can add a new column to
this view, at least I don't see it in the menu, it is deactivated. Do I have
to add a new query? Next problem is how to convert the text fields to time
when subtracting them? Does it allow negative values in the time field? It
will be very helpful even if you can help with the conversion of the fields
and a pointer where I can read about the basic things. Thank you very much
in advance.

Anamik.
Click to expand...

Cheers,
Brett
 
Close. 0 is actually midnight on 30 December, 1899, not 31 December, 1899.

And I don't think you can blame Microsoft for it: my understand is that it's
actually due to Lotus 123.

And what's with the 2 digit year in your display? Didn't we have a big fuss
about that a few years back? <g>
 
Damn, I got that day messed up again. I had it as the 30th and then
thought "nope, that's not the last day" and changed it back. Thought
it was a John Viescas typo - should have known better! ... grrr

Am suitably chagrined for blaming MS, I often do a <RANT> ... </rant>
against people who blame MS for sunspots, famine, war, pestilence and
locust swarms.

Oh yes, and the 2 digit year .... ahem.

On all 3 points .... text block appropriately ammended .. am obliged
Douglas :)

Cheers
Brett

Close. 0 is actually midnight on 30 December, 1899, not 31 December, 1899.

And I don't think you can blame Microsoft for it: my understand is that it's
actually due to Lotus 123.

And what's with the 2 digit year in your display? Didn't we have a big fuss
about that a few years back? <g>
Click to expand...

Cheers,
Brett
 
Back
Top