I have a problem, I need an sql (for access) statement that will alway return the coming sunday's date from any day in the week including sunday, can i round up in an sql statement? if i could this would be nice, then i could do omething like, ((roundup(date()/7))*7)+1 this would would work if i could round up, but i dont think i can. or if i could just drop the decimal somehow, and use. (((remove decimal(date()/7))+1)*7)+1 is there any way i can do this? any help would be greatful

I believe that functions like this vary among the different implementations of SQL, so you'll probably have to dig thru your versions documentation. Informix has a round function of the format: round(number, digits) Examples round(24536.7654, -2) = 24500.00 round(24536.7654, 0) = 24537.00 round(24536.7654, 2) = 24536.77 Informix also has Trunc() which does truncates the digits without rounding. I just looked in the mySQL doc and round works the same there. So maybe this is more standard than I thought.

I'm not sure what the function is in sql. In other languages it's trunc(), or int(), or floor(). However, they way you did your roundup using the int() is not quite correct. It's pretty close, but you'll get different results in cases where date is evenly divisible by 7. I don't know if that's what your application wants or not, but there are a couple of ways to make it really round up. The easiest takes into account that the date() is going to return an integer, and you're always dividing it by 7. (int((date()+6)/7)*7)+1 You may even want to try 6.5 to eliminate rounding errors.

thanks for the help, i found that this doe exactly what i want, (((Int((Date()-2)/7)+1)*7)+1) thi will alway return the coming sunday's date.