CODEWITHSUNDEEP
ms-accesstimereport
user3033348
user3033348

Reputation: 143

Access Report - Calculating Hours between 2 times

I have a SQL time off database with a Access front end. I currently have BeginTimeOff and EndTimeOff fields on the report. In my SQL database, these are Time(7) fields. I want a new field to show the time difference. I've tried to have the Control Source be equal to:

=DateDiff("n",CDate([BeginTimeOff]),CDate([EndTimeOff]))

AND 

=DateDiff("n",[BeginTimeOff],[EndTimeOff])

AND

= [EndTimeOff] - [BeginTimeOff]

I can't get anything to work. I can subtract dates fine, just not times. Help!

Upvotes: 1

Views: 66

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123799

Access does not have a time-only field type (Access Date/Time fields have both a date and time component), and any unknown field types in an ODBC linked table are usually mapped to Text. So if you have a SQL Server table with time(7) columns ...

CREATE TABLE [dbo].[TimeTest](
    [Id] [int] NOT NULL,
    [BeginTimeOff] [time](7) NULL,
    [EndTimeOff] [time](7) NULL
    ...

then the corresponding ODBC linked table in Access will have Text(255) columns instead:

AccessTable.png

If you want to directly use the columns in the linked table then you will have to convert the values into a form that Access will accept before you can use functions like DateDiff() to do calculations with them. Specifically, Access Date/Time values do not support fractional seconds so you will have to remove them. That is,

CDate("07:59:00.0000000")

will fail with a "Type mismatch" error (run-time error 13), while

CDate("07:59:00")

works fine. You can use string manipulation functions like InStr(), Left(), Mid(), etc. to get rid of the fractional part of the string.

Another approach would be to create a SQL Server View that converts the DATE(7) columns to DATETIME

CREATE VIEW [dbo].[TimeView]
AS
SELECT
    Id, 
    DATEADD(day, -2, CONVERT(DATETIME, BeginTimeOff)) AS BeginTimeOff, 
    DATEADD(day, -2, CONVERT(DATETIME, EndTimeOff)) AS EndTimeOff
FROM dbo.TimeTest

and then if you link to that View the columns will appear as Date/Time values in Access

LinkedView.png

Upvotes: 1

Related Questions