Date calculation query

[neilp]

I am attempt to find the week number and/or the date of each Monday between two dates.
My table called [jobs] has two date fields erect_date, end_date
The end_date is calculated by entering erect_date and hire_period in days within the input form.
What I wish to do is
1- check that end_date is before now() ie. hire is overdue
end_date > now()
2 - if end_date is less before now() calculate the week number between the two dates and/or display the date of the Monday corresponding to that week

Job_no Erect_date End_date Week_no First day of wk
1 01/09/04 29/09/04 41 4th Oct 2004
2 15/08/04 30/09/04 40 27th Sep 2004

I am asking the impossible or trying to do it all in one simple query - please can someone point me in the right direction.

Neil P

 

[Pat Hartman]

I don't understand either of your questions. Do NOT use Now() unless you want to include time of day. You will have less trouble if you use Date() since that function only returns the current date rather than the date plus the time.