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.

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

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)))

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

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

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)))

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

:confused: 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

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!!! :) ]

Ah - got you. Thanks.

Ally & Harry,

Thank you both, you two were a life saver