date expression to bring up a weeks worth of records

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

Guest

i have my new data base built but for the love of god can i get the
expression right this.

in my query i need, when prompted it asks for a date, and when i put in
21/11/06, i want it to bring up all records for that week( monday to sunday).
can anyone 1 help plz.


Thank you.

Chris
 
Use a Between criteria with the function Weekday():

Between [InputDate]-Weekday([InputDate])+1 AND [InputDate]+7-Weekday(
[InputDate])
 
hi, thanks for the quick response, but if wont let me use it as its "too
complex" or typed in correctly. any other suggestions plz

Thank you

kingston via AccessMonster.com said:
Use a Between criteria with the function Weekday():

Between [InputDate]-Weekday([InputDate])+1 AND [InputDate]+7-Weekday(
[InputDate])
i have my new data base built but for the love of god can i get the
expression right this.

in my query i need, when prompted it asks for a date, and when i put in
21/11/06, i want it to bring up all records for that week( monday to sunday).
can anyone 1 help plz.

Thank you.

Chris
Click to expand...
Click to expand...
 
In your query, this should go in the criteria for the date field. Make sure
to reformat this if the posting is broken up. Additional parentheses may
help Access digest it:

Between ([InputDate]-Weekday([InputDate])+1) AND ([InputDate]+7-Weekday(
[InputDate]))

Otherwise, simply use the mathematical comparators <>=.

Also, is your system set up to show dates in the format day/month/year rather
than month/day/year? Is this how data is recorded in the table?
hi, thanks for the quick response, but if wont let me use it as its "too
complex" or typed in correctly. any other suggestions plz

Thank you
Use a Between criteria with the function Weekday():
Click to expand...
[quoted text clipped - 11 lines]
Click to expand...
 
i have my new data base built but for the love of god can i get the
expression right this.

in my query i need, when prompted it asks for a date, and when i put in
21/11/06, i want it to bring up all records for that week( monday to sunday).
can anyone 1 help plz.

Thank you.

Chris
Click to expand...

Add a new column to your query.
WeekOf:DatePart("ww",[DateField])

As criteria on this column, write:
DatePart("ww",[Week of what date? mm/dd/yyyy])

I notice you are using a non-US date format.
The date, when prompted, MUST be entered in the US format of
month/day/year unless the date is unambiguous. A date of 2/5/2006 is
ambiguous. It could be Feb 5 or May 2.
 
Back
Top