Display correct value

[mtagliaferri]

I have a form with various calculation fields to calculate and display the difference between two dates/times and rounding this up to the nearest quarter of the hour (measured in 0.25, 0.50 or 0.75 for pay calculation)

(Pic attached = Start date - End Date - Tot Mins - Hours - Mins - Round Up)

The result is displayed in a field named [BobNewNear] where the Control Source is

=NewNear([BobTotMins])

.

The minimum pay is 6 hours.

If the Control Source for [BobNewNear] has a value between 0.25 and 5.75 I would like always to display 6.00, for any calculated value above 6.00 to display the actual calculated value.

How can I achieve this?

 

[isladogs]

Iif([YourFieldName] Between 0.25 And 5.75, 6, [YourFieldName])

 

[Deleted member 147267]

Perhaps something like

IIF([BobNewNear]<6,6,[BobNewNear])
Translation; if BobNewNear is less than 6, show 6, otherwise show BobNewNear
The above assumes BobNewNear values are numeric. Since it is a calculated value, it cannot be a bound field, but you probably know it's not a good idea to store calculated values in tables anyway.


I had a choice so I went with "The minimum pay is 6 hours."

 

[mtagliaferri]

I have tried

=NewNear(IIf([BobTotMins] Between 0.25 And 5.75,6,[BobTotMins]))

But it does not seem to work.

I have tried in a new field

IIF([BobNewNear]<6,6,[BobNewNear])

This does work, but my issue is that if the value is 0 then it must display 0, I need to return minimum 6 for any values between 0.25 and 5.75.

Also can I achieve this without creating a new field?

 

[theDBguy]

I have tried in a new field

IIF([BobNewNear]<6,6,[BobNewNear])

This does work, but my issue is that if the value is 0 then it must display 0, I need to return minimum 6 for any values between 0.25 and 5.75.

Also can I achieve this without creating a new field?

To achieve this without using a new field, then I suppose you can add it to your calculation to display the correct result. Otherwise, you could try adding a check for a zero (0). For example:

IIf([BobNewNear]=0,0,IIf([BobNewNear]<6,6,[BobNewNear]))

Hope it helps...

 

[Deleted member 147267]

If field is null it's not equal to 0? I was thinking to use Nz but haven't figured the >= .25 part yet. On my phone using data so that's all for now.

 

[isladogs]

I have tried

=NewNear(IIf([BobTotMins] Between 0.25 And 5.75,6,[BobTotMins]))

You can't use IIF within a function. Perhaps this will do what you want

=IIf([BobTotMins] Between 0.25 And 5.75,NewYear(6),NewYear([BobTotMins]))

 

[Deleted member 147267]

Posts 1 and 4 are asking for an expression for a calculated control, thus sql doesn't apply? I don't think you can use Between in such an expression. I know this or any similarly bracketed expression raises a syntax error in vba
IIf([BobTotMins] Between 0.25 And 5.75,6,[BobTotMins])

but if you use
IIf([BobTotMins] > 0.24 And [BobTotMins] < 5.76,6,[BobTotMins]) it does not.

Or if you wish to use => then with different values:
IIf([BobTotMins] >= 0.25 And [BobTotMins] <= 5.75, 6, [BobTotMins])

 

[mtagliaferri]

Thanks everyone, it seems that

=IIf([BobNewNear]=0,0,IIf([BobNewNear]<6,6,[BobNewNear]))

Does my trick!!

Now I just realised I have a further condition and I am pushing my luck with this

all duties are paid a minimum of 6 hours hence the above condition, however I have duties that the employee may want to bank and not be paid; I would still record start and end Date/Time but I would need to return 0 for the above:

I have a field named Duty Type which has different one letter codes, should this letter be "B" (as banking) the above code should return 0, any other letters in the Duty Type field should process the above calculation.

Thanks again for your great help!

 

[theDBguy]

Hi. You could try:

IIf([Duty Type]='B' OR [BobNewNear]=0,0,IIf([BobNewNear]<6,6,[BobNewNear]))

Hope that helps...

 

[mtagliaferri]

Spot On!!!!!

Thanks so much to all of you !!

 

[theDBguy]

Spot On!!!!!

Thanks so much to all of you !!

Hi. You're welcome. Glad to hear it worked for you. We were all happy to assist. Good luck with your project.