Subtracting 2 "time" fields

[carl]

I have 2 fields, TIME1 and TIME2

TIME1 2008/04/10 16:10:46.795

TIME2 2008/04/10 16:10:46.000

I am trying to create a new field TIME3 that will do the following:

If TIME1 minus TIME2 is greater than 0 return "MAKE", other wise return
"TAKE".

Is this possible ?

Thank you in advance.

 

[carl]

Thanks. I ran into another problem. When importing the data, TIME1 and TIME2
cannot be converted into Date/Time Format. I need to reformat TIME1 and
TIME2, can you help me with this problem ?

 

[John Spencer]

The Date Time values you posted included fractions of seconds. The Access
DateTime field is only accurate to seconds.

You may need to store the date and time in one field (a date time field) and
the fractions of seconds in another. OR just the date in a datetime field and
store the time as a count of seconds and partial seconds in a number field
(Type: Double). You will have to work out the conversion algorithm

TimeInSeconds:
DateDiff("s",0, TimeValue(LEFT(Yourfield,Len(YourField)-4)))
return the total number of seconds

TimeMilliSeconds: Val(Right(YourField,4))

Adding those together, you would store.

DateDiff("s",0, TimeValue(LEFT(Yourfield,Len(YourField)-4))) +
Val(Right(YourField,4))


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County