Beginner (Need Help)

[bessej43]

I have three Iif statement that I would like a module for: They are:

A. (0)zero = Balance2:IIf([Leave]-Fix([Leave])=0.0,Fix([Leave]),Fix([Leave])+0.0)
B. (>=1-5<=)= Balance :IIf([Leave]-Fix([Leave])>0.5,Fix([Leave]),Fix([Leave])+0.5)
C. (>=6-9<=)= Balance1:IIf([Leave]-Fix([Leave])<0.5,Fix([Leave]),Fix([Leave])+1.5)

Would it be something like this?

Option Compare Database
Option Explicit

Function BALANCE(NumberArg As Double) As Double
If NumberArg = Fix(NumberArg) Then ' Evaluate argument.
BALANCE = NumberArg
ElseIf (NumberArg - Fix(NumberArg) > 0.5) Then
BALANCE = Fix(NumberArg) + 0.5 '
Else
BALANCE = Fix(NumberArg) + 1.5
End If
End Function

 

[Randomblink]

SELECT CASE vs IIF

instead...try...

Function Balance(NumberArg As Double) As Double
Select Case NumberArg
Case Fix(NumberArg)
Balance=NumberArg
Case NumberArg -Fix(NumberArg) > 0.5
Balance = Fix(NumberArg) + 0.5
Case Else
Balance = Fix(NumberArg) + 1.5
End Select
End Function

I might be a little messed up in the math...
But I think you get the gist...

Try it out...

 

[bessej43]

It's changing numbers that I don't want to change. Can this be inserted somewhere in the statement?

>=0.1 and <=0.4 then +0.5

0.5 leave alone

>=0.6 and <=0.9 then +1.0

0.0 leave alone

 

[Randomblink]

Brutality

To be brutally honest...

I am not sure what you are doing with this...

If I am gonna help, you gotta explain like I am a kindergartner...

What are you wanting to check...
Once you check it, what are you wanting to do with it...
I can prolly help ya, but I need more information...

 

[bessej43]

I am trying to take the system date - start date and come up with total number of days:

System 6/17/02
Start Date 9/30/01
260 days

Then take the number of days (260) divided by the every 6th day.

Days 260
divided by 6
Days by leave 43.33333333

Then you get 0.5 days for every 6th day

Days by Leave 43.33333333
Half a day per day 0.5
Total leave days 21.66666667

Then round up to 22 days (mbr get 22.0 days of leave)

vs

System 6/17/02
Start Date 9/22/01
268
6
44.66666667
0.5
22.33333333 **(see query below)


Then round up to 22.5 days.

The number of leave days should end as a 0.5 or 0.0

I wrote a query to do the calulation for the total leave days

**Leave: ((((((DateDiff("y",[START DATE],Now()))/6*0.5)))))

22.33333333

Need rounding statement for:

if >=0.1 and <=0.4 then roundup to 0.5

if 0.5 leave alone

if >=0.6 and <=0.9 then roundup to +1.0

if 0.0 leave alone

I hope that I have explain it well enough

Thanks for all your help

 

[cogent1]

You can do this with the following code where you enter the Start date on a form field called MyDateField. The result comes out in MyLeaveField, correctly rounded.

Dividing by 6 then multiplying by 0.5 is equivalent to a single divide by 12 operation. by the way.

Private Sub MyDateField_AfterUpdate()


Dim Fraction As Single
Dim NumberOfDays As Single

NumberOfDays = DateDiff("d", [MyDateField], Now)/12

Fraction = NumberOfDays - Int(NumberOfDays)


Select Case Fraction

Case 0 'do nothing

Case Is >= 0.5
NumberOfDays = Int(NumberOfDays) + 1

Case Else
NumberOfDays = Int(NumberOfDays) + 0.5


End Select

[MyLeaveField] = NumberOfDays
End Sub

Hope this helps

 

[Cosmos75]

Useful!!

cogent1,

I will find a use for this! Cool!