Calculate Time Different and Show Result under Certain Criteria in Query

[yolo]

ID Up Time Down Time
John 18:00 15:00
Kelvin 08:00 08:05
Melisa 23:00 02:00

This is the Table , i need the Query result show IF [Up Time]-[Down Time] is >=3 hrs
Like Below :


ID Up Time Down Time
John 18:00 15:00
Melisa 23:00 02:00


IDUp TimeDown TimeJohn18:0015:00Melisa23:0002:00

Please Help me with this , i Tried DateDiff("h",[Up Time],[Down Time]) in Field but POP out with error "Syntax error (comma) in query expression 'Table1.[DateDiff("h",[Up Time],[Down Time])]' "

Thanks A Lot

 

[plog]

What data types are [Up Time] and [Down Time]? To use the DateDiff function they need to be dates. If they are strings, you can convert them to dates by using the CDate function(http://www.techonthenet.com/access/functions/datatype/cdate.php)

 

[yolo]

The Data type for [Up Time] and [Down Time] is "Date/Time"

 

[JHB]

If you are using this syntax then it is wrong:

Table1.[DateDiff("h",[Up Time],[Down Time])]'

Is has to be like that in the SQL view:

Select SomeFields , DateDiff("h",[Up Time],[Down Time]) As [TheNameYouWant] ....

In the QBE window, then you have to replace "," with ";"

DateDiff("h";[Up Time];[Down Time])

 

[Brianwarnock]

Whether it is "," or";" depends on your locale, in the UK and US it would be a "," we do not know where Yolo is but given the error message I guess JHB. Is correct.
However the real problem is that without a date in the field Melissa will return a negative.

Brian

Corrections thought Melissa had gone over midnight

 

[yolo]

If you are using this syntax then it is wrong:
Is has to be like that in the SQL view:
In the QBE window, then you have to replace "," with ";"

It doesnt work , pop out with "The expression you entered contains invalid syntax"
i change the Query field code from "," to ";" but it pop out with that msg

 

[yolo]

Whether it is "," or";" depends on your locale, in the UK and US it would be a "," we do not know where Yolo is but given the error message I guess JHB. Is correct.
However the real problem is that without a date in the field Melissa will return a negative.

Brian

All return 00:00 ....

 

[Brianwarnock]

I made a mistake regarding Melissa as I thought that the period had gone over midnight but I now see that it had not, however you should be aware that 18:00 15:15 will reurn 3 hours despite it not being, you need to work in minutes.

I have no idea why you are getting 00:00

http://allenbrowne.com/casu-13.html

Is worth a read

Brian

 

[yolo]

after i read the website i wrote this in one field

Time: DateDiff("n",[Down Time],[Up Time])

and i wrote this in the next field

Time Down: [Time]\60 & Format([Time] Mod 60,"\:00")

when i tick show for the Time field the result will come out , if i untick it , it will need me to "Enter parameter value for Time" , how come it will happen ? where goes wrong ??
and for the Time field all result return 00:00

 

[yolo]

I made a mistake regarding Melissa as I thought that the period had gone over midnight but I now see that it had not, however you should be aware that 18:00 15:15 will reurn 3 hours despite it not being, you need to work in minutes.

I have no idea why you are getting 00:00

Is worth a read

Brian

previous post a reply for you , thanks a lot