If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Subtracting 2 "time" fields
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. |
#2
|
|||
|
|||
Subtracting 2 "time" fields
carl wrote:
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") -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Subtracting 2 "time" fields
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" wrote: carl wrote: 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") -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Subtracting 2 "time" fields
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. -- Marsh MVP [MS Access] carl wrote: 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" wrote: carl wrote: 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") |
#5
|
|||
|
|||
Subtracting 2 "time" fields
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 Marshall Barton wrote: 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. |
Thread Tools | |
Display Modes | |
|
|