beginner question

[Johnny D]

first: i'm new to using newsgroups in this way. there are a zillion access
groups and i tried this one first.

i am a rank beginner with access. some training but not a lot

i'm working on a small database to gain experience.

i have a field that is simply time in hours:mins:secs i see no time format
that accommodates this. am i incorrect? how can i set up a field for time
that isn't time of day simply duration.

thank you,

if i'm posting to wrong group please direct me to the full name of the
proper group.

John

 

[strive4peace]

Date/Time Data Type
~~~

Hi John,

you have come to the right place

the data type of the field in the table design will be date/time

the Format will be: hh:nn:ss
(m is months)

Access stores date/times in a numeric format where the integer portion
of the number represents the date and the decimal portion of the number
represents time:

1/1/100 --> -657,434
1/2/100 --> -657,433
12/30/1899 --> 0
1/1/1950 --> 18,264
1/1/2005 --> 38,353
12/31/2007 --> 39,447
1/1/9999 --> 2,958,101
12/31/9999 --> 2,958,465

the time is a fraction of the day

12 noon is 0.5
6pm is 0.75

1/1/2005, 12 noon --> 38353.5

if you have a control with just a date and you want to make sure it
converts to a whole number (or it is stored in text format), use

DateValue([control_or_fieldname])
or
cLng([control_or_fieldname])

likewise, if you have a time, you can force it to the fractional part by

TimeValue([control_or_fieldname])

since dates are whole numbers and times are the fractions, you can also
do arithmetic operations on them

that is why you can subtract one date from another and get the number of
days between the two.

Because dates can also have a time component, it is handy to use
DateDiff and DateAdd, which let you specify the time increment (year,
month, day, hour, etc) to calculate new dates or get a difference
between dates.

The DateDiff function can be used to specify what time increment you
want returned when you subtract dates

Likewise, there is a DateAdd function to add specific time increments to
a date



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[a a r o n . k e m p f]

yah I tihnk that SQL Server 2008 is coming out with some timespan
datatype.. but I really don't understand the point.

record the field as date/Time and then do the subtraction that you
need.

I think that the datetime field is superior

 

[tedmi]

The Date/Time datatype is meant primarily for representing a point in time,
rather than a duration in HH:MM:SS, and making it serve the latter purpose
takes some doing, especially if you want to perform arithmetic on it. In your
table design view, set the Format of the field to "hh:nn:ss" (that's not a
typo - minutes are indicated by nn because mm is used to indicate months).

Adding and subtracting such fields yields proper mod-60 results, but for
differences you need to provide a Format function in your query. In the Field
row of the query design grid, enter:
DurationDiff:Format(Time1-Time2, "hh:nn:ss")
DurationDiff can be any name you wish to see in your output; Time1 and Time2
should be whatever you named your time duration fields.

Multiplication (e.g. Pay = Duration*PayRate) requires that Duration be
converted from HH:MM:SS representation to fractional hours in a Double
datatype:
FracHrs = DatePart("h", Duration) + DatePart("n", Duration)/60.0 +
DatePart("s", Duration)/3600.0

(The .0 on the constants assures that Access converts the DatePart result
from integer to double - not strictly necessary, but defensive programming in
case Access default behavior changes in a future version.)