Help rounding (1 Viewer)

[bessej43]

I am trying to write a query to round the calculation to(0.0) or (0.5). I have read all the help menu but can not figure it out.

Query:

Round(((DateDiff("y",[Startdate],Date()))/6*0.5),1)


QUERY Item
System Date 6/12/02
Start Date 1/16/02
sub (total days) 147
~six day per leave day 6
# of qtr 24.50
per qtr 0.5
Total 12.2

SHOULD BE
JAN 1.5
FEB 2.5
MAR 2.5
APR 2.5
MAY 2.5
JUN 1.0
TOTAL 12.5

Any assistance would be greatly appreciated. Thanks.

 

[bessej43]

Query: Rounding Start Date and System Date

All:

Worked on the query for awhile and kind of figured it out. It required three separate IIF statements. Need help putting them together.

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

Need help joining these three statement into one query

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)


Thanks

 

[Ally]

If it's just a case of joining the IIf's together this should work.

Before the last ) insert a comma, and your second IIf, then before the last ) insert another comma and your third IIf.

ie:

IIf([Leave]-Fix([Leave])=0.0,Fix([Leave]),Fix([Leave])+0.0,IIf([Leave]-Fix([Leave])>0.5,Fix([Leave]),Fix([Leave])+0.5,IIf([Leave]-Fix([Leave])<0.5,Fix([Leave]),Fix([Leave])+1.5)))

 

[bessej43]

Ally,

Thank you for your help. I am getting a error message saying:

"The expression you entered has a function containing the wrong number of arguments."

So I deleted the ")"'s that were highlighted and still getting another error message:

"The expression you entered is missing a closing parenthesis, bracket(]), or vertical bar (l)."

Is there a certian number of "(" that must appear in the front and the back of the formula? I am using Access 2000

Again thanks for all your help!!!!!

Jesse

 

[simongallop]

Cut and post the formula as it is most probably a syntax error and can only be found by looking at it

 

[bessej43]

Harry,

I cut and pasted Ally case joining directly from the message. I went back and looked at it again. So I cut and pasted what is in the query below:

IIf([Leave]-Fix([Leave])=0.0,Fix([Leave]),Fix([Leave])+0.0,IIf([Leave]-Fix([Leave])>0.5,Fix([Leave]),Fix([Leave])+0.5,IIf([Leave]-Fix([Leave])<0.5,Fix([Leave]),Fix([Leave])+1.5)))

 

[simongallop]

Sorry didn't need you to paste the code as I have now bothered to read it!! The problem is that and Iif statement only has three parts being:

Iif(Thing being tested, Action when true, Action when false)

In your statements you have 4 parts, which is wrong.

Therefore you need to write you statement as:

Iif(=0, 0, Iif( <0.5, 0.5,1.5)) or whatever it is. I have to admit to being a bit confused by your statements as am not sure what is going on. The Iif statement only needs to have 2 Iif's as it is saying If 0 then do then 0 stuff otherwise if <0.5 do 0.5 stuff otherwise do the >0.5 stuff as that is all that is left.

HTH

 

[Ally]

Bit confused there Harry - don't mean to be dense, but where is the 4th part?

IIf([Leave]-Fix([Leave])=0.0, Thing being tested
Fix([Leave]), Action when True
Fix([Leave])+0.0) Action when False

 

[simongallop]

IIf([Leave]-Fix([Leave])=0.0,Fix([Leave]),Fix([Leave])+0.0,IIf
IIf(-----Testing--------------,---True---,----False-----,4thpart...)

([Leave]-Fix([Leave])>0.5,Fix([Leave]),Fix([Leave])+0.5,IIf([Leave]-Fix([Leave])<0.5,Fix([Leave]),Fix([Leave])+1.5)))


Ought to be:
IIf(------Testing----,---True---,False being the next IIf{or can be done on true})


[And why can't the viewing screen be the same as the edit screen in fontsize!!! ]

 

[Ally]

Ah - got you. Thanks.

 

[bessej43]

Thanks Ally & Harry (rounding)

Ally & Harry,

Thank you both, you two were a life saver