By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 2,139 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

how to break the bounds of 59 in date/time data type

P: n/a
x
hi
i am a pilot by profession. i want to create a database of my logbook
using ms access 2002. i am facing a problem regarding the format of
time field. when i select "Data/Time" data type for my time field then
this format gives the liberty to record times uptill a figure of 59 in
different sub-formats, whereas i want the format to be able to record
the times like 80:35 or 1:10 or 1138:00. which means that i have these
many hours on a specific aircraft. i also want to carryout sum
operation on these timings. i have found out that i can use "[hh]:mm"
format in ms excel 2002 to suite my requirements but it doesn't work in
access.
i am a very basic user of access or for that matter any database.
please consider this while replying my question. i have no background
of vb code which means that i will only be able to work the things out
which are not very advanced (point and shoot mouse user).
Thanx in advance and please be eleborate in your reply.
bye and tc

Feb 16 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
x wrote:
time field. when i select "Data/Time" data type for my time field then
this format gives the liberty to record times uptill a figure of 59 in
different sub-formats, whereas i want the format to be able to record
the times like 80:35 or 1:10 or 1138:00. which means that i have these
many hours on a specific aircraft. i also want to carryout sum
operation on these timings. i have found out that i can use "[hh]:mm"
format in ms excel 2002 to suite my requirements but it doesn't work in
access.
i am a very basic user of access or for that matter any database.


Hi,

When one designs a database, it's necessary to determine what data
entities you want to track and how each entity is to be represented.
This is what you are doing here, but I think you've chosen the wrong
data type for your time field. A date/time field is meant to record a
calendar date and clock time. However, what you need to record is not a
date or a time (though, of course, doubtless you'll wish to record the
date a flying entry takes place) but rather a QUANTITY where the
quantity is a measure of time. Ultimately, what you want to do is
determine the total number of hours/minutes you've flown (ie, a complete
total, or by aircraft type, by date range, or what have you).

The difficulty you (and many of us, including me, incidentally) have is
that it's a bit of a mind twist to get around "x hours and y minutes".
But what you have to do is ultimately figure out what is the actual unit
of measure for your flying time is. I would suggest it's MINUTES.

So, what you need is a field called "Flight_Minutes" or something with a
numeric data type. Now, there are various number formats, and I would
choose a Long Integer (some might choose a short integer, but what the
heck...). What you want to record in your field is number of minutes.
Thus, the examples you chose, what would go into the field would be (i'm
assuming your format is hours:minutes):

80:35 would be 80 hours times 60 minutes plus 35 minutes: 4835 recorded
in the "Flight_Minutes" field, and so on.

Now... 80:35 is easily understandable but your colleagues will look at
you like you're cracked if you tell em "ya I have 4,835 minutes in dat
model". 8) But this is an issue with DISPLAY of your information and
DATA ENTRY of your information.

Display and data entry are handled by using forms. Read up in the help
on how to do this and ask here when you hit stumbling blocks.

To display your minutes in an acceptable format, we use functions and
expressions which Access provides. To DISPLAY your information, I would
have a form that is set up around a query (the correct term is "bound to
a query") that pulls out the information you want to display (type of
model, time period, etc, etc). You will set up a text box on the form
that has a formula in it that references the Flight_Minutes field of the
query and calculates what the display should be:

=int([Flight_Minutes]/60) & ":" & [Flight_Minutes] mod 60

The int function divides flight minutes by 60 and returns the result
without any fraction, ie, 4835 divided by 60 = 80.58333, but the int
function will return just an 80. The mod operator does the division but
only returns the remainder. The remainder of 4835 is 35, thus the
expression would return 80:35.

To data enter your times, it is slightly more complicated. It is always
proper practice in any database to use forms for data entry rather than
directly enter your time into the database, but wih a little bit of code
(don't be scared!) you'll be able to type 80 and 35 into a form and
Access will convert it to minutes for you.

However, perhaps it's best to master the display part first and then we
can work on the data entry part.

I'm a long winded twit, and it may well be someone has answered this for
you much more breifly than I!

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Feb 16 '06 #2

P: n/a
x
thanx dear
it was no doubt a rewarding xperience to learn from you
bye and tc

Feb 17 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.