creating a query to set a specific date

[Cheanir H via AccessMonster.com]

hi

i have an MSAccess about a state agent, i want to create a query that
remindes me about the rents due by the tenant within the next 7 days,however
i have contract start date field where every month from the start of that
date rent will be paid for example if 07/08/05 is the contract start date
then 07/09/05 the rent has to be paid also i want this process to be reminded
automatically once i log on to the system please

can anyone help me i am realy stuck please

 

[Wolfgang Kais]

Hello Cheanir.

i have an MSAccess about a state agent, i want to create a query
that remindes me about the rents due by the tenant within the next 7
days,however i have contract start date field where every month from
the start of that date rent will be paid for example if 07/08/05 is the
contract start date then 07/09/05 the rent has to be paid also.
i want this process to be reminded automatically once i log on to the
system please

Create a
Function NextMonthlyDate(StartDate As Date) As Date
Dim dtmNext As Date
dtmNext = DateSerial(Year(Date), Month(Date), Day(StartDate))
If dtmNext < StartDate then dtmNext = StartDate
If dtmNext < Date then stmNext = DateAdd("m", 1, dtmNext)
NextMonthlyDate = dtmNext
End Function

In your query, create a calculated field
DaysInFuture: NextMonthlyDate([StartDate]) - Date()
and use as criteria: <=7

Create a form that displays the data and fom the database window
choose tools, start... and select your form as startup form.
Copy a link to the database to your windows autostart folder.