Problem with calculation from date criteria

[s_m_chamberlain]

Hi, I have a membership database I'm working on and the problem I'm tearing what's left of my hair out over is this:

If the date of joining the club is <= 15th of that month then the renewal date is 1st of that same month in the following year.

However, if the date of joining the club is >15th of that month then the renewal date is 1st of the following month in the following year.

I hope that makes sense/ Example:

JoinDate = 03/08/06 then RenewalDate = 01/08/07

BUT IF

JoinDate = 16/08/06 then RenewalDate = 01/09/07

Any ideas on how I would go about doing this would be greatly appreciated.

I realise that I could simply use the DatAdd() function to add an entire year to the JoinDate, but as you see the criteria I have to work to is unfortunately not that simple.

Thanks hopefully.

 

[RV]

Use this as an expression in a query column:

IIF(Day(JoinDate)>15, DateSerial(Year(JoinDate)+1, Month(JoinDate)+1, 1), DateSerial(Year(JoinDate)+1, Month(JoinDate),1))

Could be that you need to replace the comma's by semi colons.

RV

 

[s_m_chamberlain]

Thanks I'll give that a go.

 

[s_m_chamberlain]

Thanks very much RV - that worked an absolute treat!