seperate date and time fields

[supmktg]

I'm working with a linked table that has a seperate field for the date, and a seperate field for the time (which show in the linked table as '11/30/1899 9:46:00 AM'). I need to calculate the time difference between 2 entries.

How can I merge the 2 fields into a combined date/time field for my calculations?

Thanks,

Sup

 

[Cosmos75]

Assuming you have the following two fields, dtDate (Date, e.g. 6/12/2006) and dtTime (9:00:00 PM), you can do the following.

[B]dtCombine:[/B] DateSerial(Year([dtDate]),Month([dtDate]),Day([dtDate])) + 
           TimeSerial(Hour([dtTime]),Minute([dtTime]),Second([dtTime]))

There may be a better way but this is how I do it.

You can add the two together. Dates are stored a a number and dipleyed in a date and/or time format. The left of the decimal is the time and thte right of the decimal is the data.

But just because you are shown 9:00:00 PM, the actual value could represent any date and that same time. The same if true of the date, of it displays 6/12/2006, if could also contain the decimal for any time during that day.

Hence the reason for the long formula, which only takes the parts you want from each field.

Perhaps someone more knowledgeable than myself has a better solution.

Hope that helps!

 

[supmktg]

Thanks for the quick reply!!!

DateSerial(Year([dtDate]),Month([dtDate]),Day([dtDate])) +
TimeSerial(Hour([dtTime]),Minute([dtTime]),Second([dtTime])) returns exactly what I want. At least it looks exactly like I want? But when I try to update my date/time field using it, I get a type conversion failure. How can I fix this?

Sup

 

[supmktg]

I figured out the type conversion problem and got it working.

Thanks very much for your help!!!

Sup

 

[Cosmos75]

supmktg,

Glad to be of help!