Date time calculation from strings

[rjacko10]

Hi there,

I have four columns: date1, time1, date2, time2. All are strings.

I want to concatenate date1 & time1. Then date2 & time2.

Then I want to do the following calculation and the answer to expressed as number of days:

datetime2 - datetime1.

Please note: I am using MS Access 2007 and the results are being grouped by a different variable. Ideally I want to complete this action as one complete statement in the SELECT statement of my query. This is very easy to do in Excel but I'm baffled by MS Access!

Any help would be hugely appreciated!!!

 

[plog]

First, a bunch of whys. Why do you have the data stored as strings? Why do you have seperate fields for Date and Times? Why do you need the time field if the output is to be in Days?

If you have valid reasons for all of those, then you would use the DateValue function (http://www.techonthenet.com/access/functions/date/datevalue.php) to convert your data to dates, then the DateDiff function (http://www.techonthenet.com/access/functions/date/datediff.php) to determine the amount of days between them.

Again though, your table is most likely structured improperly.

 

[rjacko10]

Hi Plog,

You're right, I'm working with someone else's construction (a database via ODBC), the data is stored incredibly inconveniently. But I need the time field because I am looking for decimal points (I need to be precise.. clinical research).

Your help was great. Thanks.

From that I found the TimeValue function. Still not 100% sure how to concatenate them yet. But because I've also found NULL values in these fields I've decided to run a 2nd query for formatting purposes to help keep the primary query simple.