Question Calculating time difference from a date/time field

[gessie_00]

Hi,

In a table I have a field that combines both date and time, in the format of "dd-mm-yyyy hh-mm". The field type is "Date/time" (translated from the Dutch version, in case it's a little different). I have another field in the same table with the same type and format.

My question is this: In a form, can I take the time ("hh-mm") from one of those fields and deduct it from the time of the second field (also "hh-mm"), and automatically enter the result in a third field? If so, how?

I know it would be best to split it into a "date" and "time" field, but I've imported a large sheet from Excel, so that's not really an option, since I'd have to enter all that data manually.

With kind regards,
Gessie

P.S. I'm using Access 2007.

 

[Rabbie]

in Access the format of of a date/time field is a long number. The bit before the decimal point gives the date and the bit after the decimal point gives the time. If you multiply the time part by 24 you will get the hours value

 

[Brianwarnock]

I take it that you are not concerned with the Date part going over midnight?
then
Format(([date2]-[date1]),"hh:nn")

will give you what you want. This will be a string therefore you may need to wrap Cdate round this.

Brian

 

[neileg]

Simple subtration that Brian suggests works OK. However, the 'by the book' method uses the function DateDiff() which handles date/time data and returns an answer formatted however you want it.

Splitting date and time isn't usually beneficial since Rabbies explanation points out how the date/time datatype works.

 

[The_Doc_Man]

Also, you mentioned storing computable data in a table field. This is usually not a good idea, though exceptions exist. When you need differences, compute them in queries rather than storing them statically in a table. The idea is to not store anything you can easily recompute because making the record bigger (by adding another field) will eventually make accessing the table slower.

 

[gessie_00]

Thanks a lot for your replies.

I figure I should use the following expression and place it in the text field's "Control Source" property:

=DateDiff([m];[Aanvang];[Gereed])

The "Aanvang" field is the first date, the "Gereed" field is the second date. "m" stands for "minute" (not sure if it's only that way in the Dutch version). It's not working however, the text field only contains the text "#Naam?" (name).

The Doc Man, you're right, but I didn't mean a table field. I meant a field in the form, as in, a text field. I'm guessing it's not called a "field" in this context.

P.S. Brian's suggestion does work. But I'd like to get DateDiff to work as well. For anyone who'd like to know the exact solution, I entered the following expression in a new text field's "Control Source" property:

=CDate(Format(([Aanvang]-[Gereed]);"Korte tijdnotatie"))

Where "korte tijdsnotatie" means "short time notation", or basically what Access turned "hh:nn" into.

Edit: Could I somehow make a query which uses this "new text field"'s result in its criteria? If not, how can I enter this calculation in a query? Or would it be better to put the time which resulted from the calculation in the table? If so, how?