Rounding dates

[stephen81]

I am creating a membership database at the moment. When I create a new member, I put the start date and expiry date of their membership. I want the start date to always be the first of a month, but I need it to be the closest first of the month.
i.e. If I set up a member on 05 May, I want the start date to be 01 May. If I set up a member on 20 May, I want the start date to be 01 June as that is closer. Is there any functions that will round dates to the nearest month? Or does anyone have any suggestions as to how I can achieve this?

Thanks in advance.

 

[KenHigg]

I would think the easiest thing would be to do a custom function...

 

[ColinEssex]

why would you need a function to do this? usually if the start date is >15th then you just enter the 1st of next month.

Col

 

[KenHigg]

That sounds like it would work

I guess a little more info would be needed to make an appropriate suggestion...

 

[FoFa]

Basically I would think something like this:

Function RoundDate(CheckDate as date) as date
DIM SDate as date, EDate as date
DIM Ddiff as integer, Ediff as integer
SDate = dateserial(Year(CheckDate),Month(CheckDate),1)
EDate = dateserial(Year(DateAdd("m",1,CheckDate)),DateAdd("m",1,CheckDate),1)
Ddiff = ABS(datediff("d",SDate,CheckDate))
Ediff = ABS(datediff("d",EDate,CheckDate))
IF Ddiff < Ediff then
RoundDate = Sdate
ELSE
RoundDate = EDate
END IF
End Function

 

[ColinEssex]

That sounds like it would work

I guess a little more info would be needed to make an appropriate suggestion...

like this you mean?

I would think the easiest thing would be to do a custom function...

Home time now Ken

Col

 

[KenHigg]

.
.
.
.G'day Col

 

[stephen81]

Thanks for the suggestions. I figured out a custom function in the end. There's no real reason why I couldn't just test if the date was >15th Colin as that would work generally, I just wanted to round it so that it works accurately even in February. Makes very little practical difference, but I'm picky sometimes!