Hmmm.... You have this:
IIf(DateDiff("n", [time out], [time in] - [delay mins]) Between 0 And 45
When you should have this:
IIf(DateDiff("n", [time out], [time in]) Between 0 and 45
I think part of the problem is that doing it inline using the IIF() function
is confusing you. It's not as elegant and "tight" but let's make it easier
by breaking it out. Try this:
'************ Start Code ***********
Dim intMinutes as Integer ' Put this at the top of your sub
with the rest
' of your Dim statements
' The NZ() function takes care of any Nulls in the controls
intMinutes = NZ(DateDiff("n", Me.txtTimeOut, Me.txtTimeIn),0)
If intMinutes <= Me.txtDelayMinutes then
intTotalTime = 0
Else
intTotalTime = intMinutes - Me.txtDelayMinutes
End If
'************ Start Code ***********
The above code places the final result into an integer variable called
intTotalTime. If you want to place the final result into a control on your
form, change "intTotalTime" to the name of the control (e.g. Me.TotalTime).
A few other things you should note regarding the above code...
1. I've replaced the "Between 0 And 45" with "<= Me.txtDelayTime" (i.e.
"less than or equal to the value in the DelayTime control") so that you can
change the value of the Me.txtDelayTime control and the formula will self
adjust. If you use a number instead of a variable, you'll have to change the
code if you ever want to change the value of "delay time."
2. I have changed your use of fields (e.g. [time out]) to using controls on
the form (e.g. Me.txtTimeOut). Of course, this assumes that you're putting
the subroutine in the code behind the form. If you're using a subroutine or
function in a module, you cannot use the "Me" keyword.
3. I've taken the spaces out of the field, control, and variable names.
Having spaces in names is generally not a good idea (it can cause problems).
To make the names easier to read, capitalize each word (e.g. Me.txtTimeOut
instead of Me.txttimeout). You can also use underscores (e.g.
Me.txtTime_Out) but I find that rather messy and awkward to work with. It's
all personal preference, of course.
4. I'm also using standard nomenclature to make it easier to understand the
type of variable (e.g. intSomething for integers, strSomething for strings,
txtSomething for a text box control, etc.) Again, this is generally accepted
practice and makes the code easier to read and understand down the road.
Uhhhh.... One other thing I just noticed when rereading your post.... You
said you're using the expression box for the code. The above is NOT put into
the expression box. Instead, put it in the code behind your form. The best
place would probably be in the form's BeforeUpdate event.
I hope the above has helped. If not, maybe post your actual code so we can
take a look.
Regards, Chris
bob said:
Thanks for the info, but it is not working the right way if you put in (ex.
12:00:00 time in and 12:42:48 Time out it will show 0.00 which is the way it
should but when you put in 12:00:00 time in and 12:50:00 and it is a positive
number it shows the whole number of 50 minutes it is not taking the 45 mins
away. I did try this in the expression box IIf(DateDiff("n",[time out],
[time in]-[delay mins]) Between 0 And 45, 0,DateDiff("n",[time out], [time
in]-[delay mins])) butit is not working
thank you for your time
Chris O''''Neill said:
Uhhh.... don't you mean DateDiff, Allen? I think the syntax should be as
follows:
IIf(DateDiff("n",[time out], [time in]) Between 0 And 45, 0,
DateDiff("n",[time out], [time in]))
Or, you could change the "Between 0 and 45" to "< 0" and get the same
result. Btw, having it the other way around [time in] first (i.e. the second
parameter) would yield a negative number, and I think he wants to multiply
the result by a rate so doing it this way means he doesn't have to reverse
the negative result.
Hope that's helpful...
Regards, Chris
P.S. I tremble at even the though of correcting an Access Wizard such as
yourself. I'm gonna feel REALLY stoooooopid if I'm wrong!
Allen Browne said:
So you want to show a zero if the [time out] is up to 45 minutes before
[time in]?
IIf(DateAdd("n",[time in], [time out]) Between 0 And -45, 0,
DateAdd("n",[time in], [time out]))
(Not sure about whether the in or out should be first in those
expresssions.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Hi, there again
here is another issue that i need to resolve i have a time in and time out
with a 45 min grace period and that all works thanks to you but here is
the
issue how can i get it to show a 0 if it is a negitive number. Meaning
that
it is less tthen 45 minutes. This is all calculating into a field for a
total
but it shows a negitive and then when there is a positive number it will
subtract from it Note: there are several of these boxes that calculate the
in
and out time with delay time
Time in 12:00:00
Time out 12:42:00
Delay min 45
Total Mins -3.00
I want the total mins to show 0 so that the total time is 0 and the pay is
0
Total time -3.00
Total pay ($0.95)
In my earlier post, I said the