I have a table that has a field that lists the first monday of each
week. So there might be records as follows, 12/30, 1/6, 1/13. I would
like to find an expression I can place in a query that will determine
the date for the monday of the current week. So for example, this week
it would return 12/30. This way I could know which records belong to
this week.  Secondly, I would like to be able to have it determine the
monday for the next week, the week after that ecetra (how I would
manipulate the form to return this value)

Thanks for your assistance.

Hi cheaptrinkets-ga,

Solving the problem you've described requires an SQL query, but before
we can use that we need a table to test the query. I have created one
that looks like this (where the name of the table is "Dates"):

Monday     | Notes
30/12/2002   Text A
06/01/2003   Text B
13/01/2003   Text C

Now that we have something to work with, let's consider the problem.
Since your table is structured so that entries are only made for each
week, we can use the following query to obtain the row for that week:

SELECT *
FROM Dates AS D
WHERE (D.Monday <= Date()) And (D.Monday > Date()-7)

Clearly, the key to solving this problem lies in the Date() function,
which returns today's date (based on your Windows clock). Another
helpful component is that dates can be compared using the <, >, and =
operators.

In order to put this into Access, simply create a new Query, go to the
SQL View within that query, and replace whatever is in there with the
above statement.

I hope that answers your question, but if you have any problems
understanding the information above please feel free to post a
clarification :)

Cheers!

answerguru-ga
Google Answers Researcher

---

Request for Answer Clarification by cheaptrinkets-ga on 30 Dec 2002 10:23 PST

Here is the sequel from my query. 

SELECT findingmonday.Dates
FROM findingmonday;

I am a bit lost, please plug my sgl into your solution and repost.


Thanks.

---

Clarification of Answer by answerguru-ga on 30 Dec 2002 10:56 PST

Hi there,

Based on the information you have provided in your clarification
request I am assuming that the name of your table is "findingmonday"
and the field containing your date entries is "Dates". The query with
these names would look like this:

SELECT * 
FROM findingmonday AS F 
WHERE (F.Dates <= Date()) And (F.Dates > Date()-7)

That should do it...let me know if you are still having problems :)

answerguru-ga

Nice job. Thanks!

Hi cheaptrinkets-ga,

Thanks for the nice tip and rating!

answerguru-ga

Answerguru's SQL requires that the record have findingmonday.Dates
less than or equal to the current date.  In other words, it locates
the record in the table which has a value findingMonday.Dates either
today or in the past six days.

I wonder if this is the desired outcome when today's date is a Sunday.
 If Sunday is considered the first day of the week, then what
cheaptrinkets-ga asks to find is the Monday of that same week, not the
previous Monday.

Answerguru's approach can be modified to handle this case:

SELECT *  
FROM findingmonday AS F  
WHERE (F.Dates <= Date()+1) And (F.Dates > Date()-6) 
 
It might also be useful to know that the first Monday of the week for
any date can be computed, using a VB expression, for any given date
myDate:

DateAdd("d", vbMonday - Weekday(myDate), myDate)

This expression adds or subtracts the proper number of days to myDate
to get the Monday of the same week as myDate.  This again assumes
Sunday is considered the first day of the week.

regards, mathtalk-ga

Thank you. I appreciate your assitance. Please feel free to answer any
question I post.

Thanks for the kind encouragement!  I hope you are as impressed with
Hammer's stuff as I am -- he/she is one amazing researcher from what
I've been reading.

regards, mathtalk-ga

I couldn't agree more!

Thank you cheaptrinkets, and thank you MathTalk!

BTW, cheaptrinkets, I'm sorry to see that you expired your spreadsheet
conversion question. I encourage you to repost it. I am not the only
skilled Access person involved with Google Answers. As I said in my
RFC, one of the other Researchers may be willing to perform the work
you requested as the price you offered.

- Hammer