dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do i show a date between 1jan of any year, 1jun of any year, 1 sep of any
year. in a query

and then show the date it has to be renewed e.g.

-a date between the 1jun of any year and the 1sept of any year has to be
renewed in the 1sept.

-a date between the 1jan of any year and the 1jun of any year has to be
renewed in the 1jun

- if a date is between the 1sep of any year and the 1st jan of any year then
it must be renewed in 1jan
 
You write a function to return the renewal date. Something like this (in a
standard module).

function RenewDate(d as date) as date
select case month(d)
case 1 to 5
renewdate=dateserial(year(today),6,1))
case 6 to 8
renewdate=dateserial(year(today),9,1))
case 9 to 12
renewdate=dateserial(year(today),1,1))
end select
'if you want the 'next' one then add...
if renewdate<today then
renewdate=dateadd("yyyy",1,renewdate)
endif
end function

and then, on your query, add a column:

RenewalDate: RenewDate([YourDateField])
 
Back
Top