Using Format & DateDiff return WRONG result, Please help! 1

[RaffiqEddy]

Hi,

I need to get a result on the difference between 2 times (i.e.: 35min). Next, I need to convert it to “short time” format (i.e.: 00:35)

So I tried the below statement in the query:

SELECT DateDiff('n','9:00','9:35') AS mDateDiff,
       Format(DateDiff('n','9:00','9:35'),'Short Time')
       AS mFormatDate
FROM Table1;

The result was 00:00.

It was not what I expected, what I’ve miss in the statement?

Please help!

TIA & Regards.

 

[SkipVought]

Hi,

You have entered TEXT into the DateDiff arguments

SELECT DateDiff('n',#9:00#,#9:35#) AS mDateDiff,
       Format(DateDiff('n',#9:00#,#9:35#),'Short Time')
       AS mFormatDate
FROM Table1;

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[PHV]

Have you tried this ?
Format(DateDiff('n','9:00','9:35')/1440,'Short Time')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Format(#9:00:00# - #9:35:00#, "short time")

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[RaffiqEddy]

Thanks for the quick reply from both of you:

SkipVought – ur 2nd solution works, thanks for ur help, I appreciate it!

PHV -- It works, thanks for ur help, I appreciate it! – Coz of it simplicity I’m going to use ur solution & Star for u too!

Regards.

 

[SkipVought]

Do you understand WHY each of our solutions work, each with a different approch?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[tubbers]

I have a question which is sort of along the same lines.

I have two fields FirstDetect and LastDetect) in a table which are date/time types formatted in like so "5/11/2004 4:54:02 PM". I need to run a query which will select the records where the LastDetect field is a time/date earlier than FirstDetect field. Like:

SELECT Location, TagCode, FirstDetect, LastDetect
FROM MasterData
WHERE LastDetect < FirstDetect

So if FirstDetect had a value of 5/19/2004 3:45:58 PM and LastDetect had a value of 5/19/2004 1:15:48 PM, that record would be selected.

Any suggestions on how to go about doing this?

Thanks for your help.

 

[SkipVought]

tubbers,

Please post in a NEW THREAD.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}