query with dates

[Guest]

I have to run a query depending on certain dates....Every wednesday, i have
to check for the last pay period which is monday before last to last sunday.
So, for today the start date would be 10/11/04 and the end date would be
10/17/04. Both have to fall in the same month as today. Else, both are
null. So, in the month of october, the start and end dates I end up with are
.....

Start date End date
10/4 10/10
10/11 10/17
10/18 10/24 only

Any ideas on how I would translate this into a calculation I could perform
in access?

Thanks much

 

[Guest]

First of all, I would not recommend changing dates in a query. Instead,
create a form with two unbound date fields (BegDate, EndDate). In your
criteria line of your query, use the expression builder to point to those
fields. For example, your criteria might look like
Between Forms![frmInputForm]![BegDate] and Forms![frmInputForm]![EndDate]

Then, you can make the values of those dates on your form be based off a
calculation or manual input.

Like BegDate default value could be
=Date() - WeekDay(Date())+2
which would be Monday of the current week

You would be able to overwrite the date with a manual date also.
Look at "Functions for Calculating and Displaying Date/Time Value" in
Microsoft knowledgebase for more examples of date formulas.

Hope this helps. Jackie )