Office & Productivity Software forum

General discussion

Access Iif Function with short date condition

by georgebee / March 5, 2007 3:53 AM PST

He everyone,

I have a query in access where I'm trying to put in a calculated field that is an Iif function. One field in the query is "Time In" which is set to a Time Format, another field is "Time Out" which is also set to a Time Format. It is basically a time log such as time in is 8:00 a.m and time out would be say 12:30 p.m.

I then have a calculated field in the query that shows how much time elapsed between the two such as - and it is formatted to SHORT TIME so it shows 4:30 based on the example above.

Now I want to make another field that based on the Iif function something like if the hours elapsed is over 5 put YES otherwise put NO if it's false..........but my problem is that I cannot get the condition over 5 so that access sees it as a short time format.

I have tried >5:00
I have tried >5

but it doesn't see it as a short time format giving me all the false returns.....I have even tried putting it enclosed in the # symbols but that only takes it as 5:00 am.....SO MY QUESTION IS, DOES ANYONE KNOW HOW TO DO THE IIF FUNCTION AND HAVE A FIELD REFERENCED IN THE CODITION BUT TO HAVE ACCESS TAKE IT AS A SHORT TIME FORMAT

Discussion is locked
You are posting a reply to: Access Iif Function with short date condition
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Access Iif Function with short date condition
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Try .2083333333333333
by Kees Bakker / March 5, 2007 5:48 PM PST

After all, 1 is a whole day = 24 hours, so 5/24 is 5 hours. Dates and times ARE numbers in MS Office, and only FORMATTED as dates.

If you really can't get it working, write a small VBA-function with the time difference as one of the parameters. Using elementary debugging, you'll find out what to write exactly. But I don't think it's necessary.


Collapse -
DateDiff function
by richardp123 / March 5, 2007 6:07 PM PST

DateDiff function does the trick - look it up in help for full syntax/usage.

So DateDiff("h",TimeIn,TimeOut) would give the number of hours difference - in your example, 4.

If you made the query calculated expression:

ElapsedMins: DateDiff("n",TimeIn,TimeOut)/60

...this would give the number of minutes between the 2 time fields. In your example, this gives 270 minutes, then divided by 60 for hours which gives 4.5 hours.


Collapse -
Thanks a Million
by georgebee / March 5, 2007 10:38 PM PST
In reply to: DateDiff function

Hey guys thank you so much for your replies. I did the DateDiff Function as mentioned and it works like a charm. Always knew there were the parts for the Year, Months, and DAys but didn't know to use the "N" for minutes. Thank You again for assisting me with this.

Popular Forums
Computer Newbies 10,686 discussions
Computer Help 54,365 discussions
Laptops 21,181 discussions
Networking & Wireless 16,313 discussions
Phones 17,137 discussions
Security 31,287 discussions
TVs & Home Theaters 22,101 discussions
Windows 7 8,164 discussions
Windows 10 2,657 discussions


Help, my PC with Windows 10 won't shut down properly

Since upgrading to Windows 10 my computer won't shut down properly. I use the menu button shutdown and the screen goes blank, but the system does not fully shut down. The only way to get it to shut down is to hold the physical power button down till it shuts down. Any suggestions?