Date ane Time Comparison

B

Bill

Hi All,

I have imported data into Access in which dates and times were entered into
separate fields. I need to do calculations on the 'working hours' between
two times that may be on different dates and not include weekends. I also
need to do things like wok out what was the first event.

I am sure this has been covered before - can anyone point me in the right
direction please.

Ta..
Bill
 
J

John Spencer

Try combining the date and time into one calculated field.

IF these are true dateTime fields you should be able to use

DateField + TimeField to get one field that contains the time.

Or you could use the following.

DateAdd("s",TimeField*60*60*24, DateField)

I can't check those right now. Don't have Access loaded on this computer.
 
J

Jason Lepack

Hi All,

I have imported data into Access in which dates and times were entered into
separate fields. I need to do calculations on the 'working hours' between
two times that may be on different dates and not include weekends. I also
need to do things like wok out what was the first event.

I am sure this has been covered before - can anyone point me in the right
direction please.

Ta..
Bill
Click to expand...

If they are actually both Date/Time fields then all you need to do is
create a query that will add the two fields together to get a true
date/time.

Bill: (Table)
dDay - Date/Time - The field with the date
dTime - Date/Time - The field with the time

dDay, dTime
1/1/2007, 5:30
1/2/2007, 19:30

qry_bill:
SELECT [dDay]+[dTime] AS TrueDayTime
FROM Bill;
 
B

Bill

If they are actually both Date/Time fields then all you need do is
create a query that will add the two fields together to get a true
date/time.

Bill: (Table)
dDay - Date/Time - The field with the date
dTime - Date/Time - The field with the time

dDay, dTime
1/1/2007, 5:30
1/2/2007, 19:30

qry_bill:
SELECT [dDay]+[dTime] AS TrueDayTime
FROM Bill;
Click to expand...
vartype on the date and the time field give 7 - a date value

but when I add the date and time values I get a value equal to the time
value that was added.

I guess I need to force it to a number?

Regards.
Bill.
 
B

Bill

sorry - last reply was b***ocks. The added together values showed both the
date and the time value!

Same applies regarding forcing to a number though I suppose?

Bill.
 
J

John Vinson

Hi All,

I have imported data into Access in which dates and times were entered into
separate fields. I need to do calculations on the 'working hours' between
two times that may be on different dates and not include weekends. I also
need to do things like wok out what was the first event.

I am sure this has been covered before - can anyone point me in the right
direction please.

Ta..
Bill
Click to expand...

As John and Jason have said, you can simply add the timefield to the
datefield to get a complete date/time field. You might want to
actually add a new field to your table (if you're not going to be
routinely linking or importing to this old database); run an update
query to update it to the date plus the time, so your field can be
indexed and your queries will run faster.

The builtin Access date/time calculations (DateAdd, DateDiff etc.)
don't recognize "working hours". There's VBA code at
http://www.mvps.org/access/datetime/date0012.htm (and several other
areas on that excellent website) to do so.

John W. Vinson[MVP]
 
Top