By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,489 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Calculated Control (Text Box) on an MSAccess Report

P: 3
Expand|Select|Wrap|Line Numbers
  1. = IIf([MAIL_LOG_TIME] Between #07:00:00 AM# And #04:00:00 PM#), DateDiff("n"([MAIL_LOG_TIME],#04:00:00 PM#))
I am getting "wrong number of arguments" error

The result I'm trying to get is...if a time entry exists in [MAIL_LOG_TIME] that is between 7:00:00 AM and 4:00:00 PM(Formatted in medium time 00:00:00 AM/PM), how many minutes is it between the time that is entered and 4:00:00 PM?

And it would be extra helpful to calculate those minutes into hh:mm:ss

Thanks
-ray
5 Days Ago #1

✓ answered by twinnyfo

OK - so I learned something again (Thanks NeoPa). Just to provide additional options, here is an adjusted solution:

Expand|Select|Wrap|Line Numbers
  1. =IIf(TimeValue([MAIL_LOG_TIME]) > #07:00:00 AM# And TimeValue([MAIL_LOG_TIME]) < #04:00:00 PM#,Format(DateDiff("s",[MAIL_LOG_TIME],Date()+(2/3))/86400,"hh:mm:ss"),"")
The only thing to remember from the original post is that "Between" can't be used within an IIf statement.

This was fun!

Share this Question
Share on Google+
10 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,128
You have misplaced right parentheses:

Expand|Select|Wrap|Line Numbers
  1. = IIf([MAIL_LOG_TIME] Between #07:00:00 AM# And #04:00:00 PM#, DateDiff("n"([MAIL_LOG_TIME],#04:00:00 PM#)))
5 Days Ago #2

P: 3
Thanks for responding twinnyfo. I used your changes but got a different error message: The expression you entered has an invalid .(dot) or ! operator or invalid parentheses. You may have entered and invalid identifier or typed parentheses following the Null constant.
4 Days Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,128
OK - you got me to actually think about this one.

You can't use the Hashtags for date values in this instance, you need to convert your subject date/time into a string, and then compare that string to another string. Then, if you want things broken down into hours, minutes and seconds until 4:00, then you need the DateDiff() function to calculate by seconds, and divide by 86,400, which is the number of seconds in a day, which will give you a fraction of a day, converted into hours, minutes and seconds.

Sounds easy, right?

Here it is:

Expand|Select|Wrap|Line Numbers
  1. =IIf(Format([MAIL_LOG_TIME],"hh:mm:ss") > "07:00:00" and Format([MAIL_LOG_TIME],"hh:mm:ss") < "16:00:00",Format(DateDiff("s",[MAIL_LOG_TIME],Date()+(2/3))/86400,"hh:mm:ss"),"")
Welcome to Bytes!

Hope this hepps!
4 Days Ago #4

P: 3
Freakin' genius!

Thanks so much.

-ray
4 Days Ago #5

twinnyfo
Expert Mod 2.5K+
P: 3,128
I'm sure I borrowed a bunch of tidbits from other guys here on Bytes a lot smarter than I am....
4 Days Ago #6

NeoPa
Expert Mod 15k+
P: 31,273
Do be aware that if you want to check sections of a full DateTime value there are functions that allow you to do that without the need to convert anything to strings. DateValue() & TimeValue() are two such commonly used functions that are available to you.

Nevertheless, I'm still going to award Best Answer for this one.
4 Days Ago #7

twinnyfo
Expert Mod 2.5K+
P: 3,128
OK - so I learned something again (Thanks NeoPa). Just to provide additional options, here is an adjusted solution:

Expand|Select|Wrap|Line Numbers
  1. =IIf(TimeValue([MAIL_LOG_TIME]) > #07:00:00 AM# And TimeValue([MAIL_LOG_TIME]) < #04:00:00 PM#,Format(DateDiff("s",[MAIL_LOG_TIME],Date()+(2/3))/86400,"hh:mm:ss"),"")
The only thing to remember from the original post is that "Between" can't be used within an IIf statement.

This was fun!
3 Days Ago #8

NeoPa
Expert Mod 15k+
P: 31,273
Actually, that is a much better answer now Twinny.

Nice work. I know I helped, but you did all the running :-)
3 Days Ago #9

twinnyfo
Expert Mod 2.5K+
P: 3,128
You know I always want my answers to be "more better"!

:-)
3 Days Ago #10

NeoPa
Expert Mod 15k+
P: 31,273
Don't every say that to your English teacher. Nor, especially, that of your children. A perfect example of tautology.

I suspect (hope?) you already knew that ;-)
3 Days Ago #11

Post your reply

Sign in to post your reply or Sign up for a free account.