Rounding and decimal problem...sort of

[maacky99]

I have tried to search previous posts for my solution but have been unsuccessful.

In a query I have date field (datequoted) that I use in a formula (currentweek) to see how many weeks an order has been quoted for. This is what I currently have: DateDiff("y",[quotedate],Date())/7

It works to show me how many weeks something has been quoted, BUT when I either change the number of decimal places (which will round it) or use the round function - it doesn't give me what I need. I need between 0-1 weeks to show 1, between 1-2 weeks to show 2, etc. If something has been quoted for 1.4 weeks it should be in week 2 - not week 1.

Does that make sense? I'm sure there is an easier way to do this than what I'm trying.

 

[FoFa]

Add .5 to it before rounding?

 

[QTNM]

Had the same issue .. here is your answer ...

Function RoundUP (amt)

Dim num, decnum

If amt - int(amt) > 0 then ' Make sure it's not a round number (1, 2, 3)
decnum = Int(amt)
RoundUP = decnum + 1
else
RoundUP = amt
end if

End Function

HOPE THAT HELPS ..... you always want to round up, so convert the value to a integer, then add 1 to round up to the next.

 

[maacky99]

I will give that a try today - thanks for the responses.

 

[mresann]

Use this, it only takes a line:

((Date()-[quotedate])\7 ) + 1

NOTE the use of the BACKSLASH divisor operator which is equivalent to:

(Int(Date()-[quotedate]))/7 ) + 1

which is the INT function.

Either of these will work.