hh:mm to seconds from midnight

[quinto via AccessMonster.com]

I need to generate a number in second from midnight.
Example the reporting time is 7:50 AM , 84600 second in the day minus 28500
(Start Time in Seconds) =58200 Seconds from midnight

Thanks

Quinto

 

[John W. Vinson/MVP]

I need to generate a number in second from midnight.
Example the reporting time is 7:50 AM , 84600 second in the day minus 28500
(Start Time in Seconds) =58200 Seconds from midnight

Thanks

Quinto

DateDiff("s", [Reporting Time], DateValue([Reporting Time]) + 1) \ 60
& ":" & Format(DateDiff("s", [Reporting Time], DateValue([Reporting
Time]) + 1) MOD 60, "00")

 

[John Spencer]

Perhaps you want the following expression:
DateDiff("s",#00:00:00#,TimeValue([Reporting Time]))

To be really safe, you might want to test the value of Reporting Time first

IIF(IsDate([Reporting Time]), DateDiff("s",#00:00:00#,
TimeValue([Reporting Time])) ,Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[quinto via AccessMonster.com]

When I attempted to use thses expressions in a query I am getting syntax
error

Quinto

Perhaps you want the following expression:
DateDiff("s",#00:00:00#,TimeValue([Reporting Time]))

To be really safe, you might want to test the value of Reporting Time first

IIF(IsDate([Reporting Time]), DateDiff("s",#00:00:00#,
TimeValue([Reporting Time])) ,Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I need to generate a number in second from midnight.
Example the reporting time is 7:50 AM , 84600 second in the day minus 28500

[quoted text clipped - 3 lines]

Quinto

 

[quinto via AccessMonster.com]

Mr. Vinson
Although the expression works, I am getting the results in minutes. For 6:
00AM I'm getting 1080:00 and I should get or need 64800.

Thanks

Quinto

I need to generate a number in second from midnight.
Example the reporting time is 7:50 AM , 84600 second in the day minus 28500

[quoted text clipped - 3 lines]

Quinto

DateDiff("s", [Reporting Time], DateValue([Reporting Time]) + 1) \ 60
& ":" & Format(DateDiff("s", [Reporting Time], DateValue([Reporting
Time]) + 1) MOD 60, "00")

 

[John Spencer]

Both expressions work for me. I am testing in Access 2000 using an
Access Jet Database (.mdb).

Both expressions should be a single line. If you are getting a syntax
error in a query expression, I suggest you post the SQL statement you
are using.


Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

 

[quinto via AccessMonster.com]

SELECT [Daily Assignments].Date, [Daily Assignments].DriverID, [Daily
Assignments].Day, [Daily Assignments].StartTime, [StartTime]*86400 AS RepSec,
86400-[RepSec] AS SecToMid
FROM [Daily Assignments]
WHERE ((([Daily Assignments].Date)=[forms]![Operation]![servdate]));

I added 2 Expression to get the result that I needed but I'm not sure if that
is the right way to go.

Thanks

Quinto

 

[John W. Vinson/MVP]

Mr. Vinson
Although the expression works, I am getting the results in minutes. For 6:
00AM I'm getting 1080:00 and I should get or need 64800.

Sorry! Misinterpreted your question. It's much simpler then: skip the
formatting stuff and just use DateDiff:

DateDiff("s", [Reporting Time], DateValue([Reporting Time]) + 1))