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.

 

[Marshall Barton]

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".

IIf(Time1 > Time2, MAKE", "TAKE")

 

[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 ?

 

[Marshall Barton]

Import? How are you importing? If you are using a Transfer
method, then you will probably have to import to a temp
table of text fields and then run an append query to convert
(using CDate?) and insert the data to the real table.

If you can link to the external data, then you can probably
avoid the temp table and just use an append query.

 

[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