conditional math function

[daver56]

I am doing a subtraction of times on a make table query. I am pulling the data from an linked sql db that I cannot modify. Some of the dates and times are stored in a text format and as seperate fields, so I am converting them into DateTime fields when I do the make table query. I also need some time intervals, so I am subtracting time_a from time_b. The problem I am running into is there are times when time_a is null so when you subtract time_a from time_b then you end up with the time of day. here is my example for converting and joining the date.

c_time: (DateValue([enrdate])+TimeValue([enrtime]))-(DateValue([dispdate])+TimeValue([disptime]))


r_time: (DateValue([atsdate])+TimeValue([atstime]))-(DateValue([dispdate])+TimeValue([disptime]))


The problem I am seeing with doing it this way is, for example in r_time if [dispdate] and [disptime] are null the the interval appears as the actual time of day of the remaing field.


If atsdate is 01-12-2007 and atstime is 13:45 (1-12-2007 13:45) and dispdate is 1-12-2007 with atstime at 13:15 (1-1-12-2007 13:15),then I am getting the interval formated as long time as 13:45:00 (hh:mm:ss) when I should be getting 00:15:00 or fifteen minutes.

I have seen diffdate used but I dont know if it will give me differences of minutes and seconds.

Any Ideas?

Thanks in advance

 

[randy700]

Perhaps...

r_time = If(IsNull(DateValue(dispdate)),DateValue(atsdate) + TimeValue(atstime), (DateValue(atsdate) + TimeValue(atstime)) - (DateValue(dispdate) + TimeValue(disptime))

Randy