Display correct value (1 Viewer)

mtagliaferri

Registered User.
Local time
Today, 05:45
Joined
Jul 16, 2006
Messages
519
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
Code:
=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?
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    14.5 KB · Views: 117

isladogs

MVP / VIP
Local time
Today, 05:45
Joined
Jan 14, 2017
Messages
18,186
Code:
Iif([YourFieldName] Between 0.25 And 5.75, 6, [YourFieldName])
 

Micron

AWF VIP
Local time
Today, 01:45
Joined
Oct 20, 2018
Messages
3,476
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."
 
Last edited:

mtagliaferri

Registered User.
Local time
Today, 05:45
Joined
Jul 16, 2006
Messages
519
I have tried
Code:
=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
Code:
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’m here to help
Staff member
Local time
Yesterday, 22:45
Joined
Oct 29, 2018
Messages
21,358
I have tried in a new field
Code:
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:
Code:
IIf([BobNewNear]=0,0,IIf([BobNewNear]<6,6,[BobNewNear]))
Hope it helps...
 

Micron

AWF VIP
Local time
Today, 01:45
Joined
Oct 20, 2018
Messages
3,476
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.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 05:45
Joined
Jan 14, 2017
Messages
18,186
I have tried
Code:
=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
Code:
=IIf([BobTotMins] Between 0.25 And 5.75,NewYear(6),NewYear([BobTotMins]))
 

Micron

AWF VIP
Local time
Today, 01:45
Joined
Oct 20, 2018
Messages
3,476
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

Registered User.
Local time
Today, 05:45
Joined
Jul 16, 2006
Messages
519
Thanks everyone, it seems that
Code:
=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 :D

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

I’m here to help
Staff member
Local time
Yesterday, 22:45
Joined
Oct 29, 2018
Messages
21,358
Hi. You could try:
Code:
IIf([Duty Type]='B' OR [BobNewNear]=0,0,IIf([BobNewNear]<6,6,[BobNewNear]))
Hope that helps...
 

Users who are viewing this thread

Top Bottom