Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009

    Unanswered: Returning all rows that fall with in this week no matter what day it's run?

    I have a simple project w/ a simple database:

    Activities --
    ID -- AutoNumber
    CategoryID -- Number
    CurrDate -- Text
    TicketNum -- Text
    Duration -- Number
    UserName -- Text -- not being used right now
    Notes -- Memo

    Categories --
    ID -- AutoNumber
    CategoryName -- Text

    I would like to query all entries that have happend between Mon and Fri of the current week regardless of when the query is executed. I would like to use the CurrDate field for this.

    Is there a function that I can use to simplify this? Say, Where CurrDate IN (DateTime.Week)

    Here is the query I'm using so far:

    SELECT Categories.CategoryName, Activity.CurrDate, Activity.TicketNum, Activity.Durration, Activity.Notes
    FROM Activity INNER JOIN Categories ON Activity.CategoryID=Categories.ID
    ORDER BY Categories.ID, Activity.CurrDate;

    Thanks in advance for you help.


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    so you need to know the start and end of this week and then use those values in a between sub clause.

    eg select my column list where eventdate between (#startofweek# and #endofweek#)
    JET believes in dates which comply with the US style mm/dd/yyyy and IIRC can also handle ISO date style yyyy/mm/dd. the # symbol indictaes the following data is to be converted/stored as a date.

    to find the start of the week you need to use some of the date functions such as dayofweek and dateadd.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by jbedson View Post
    CurrDate -- Text
    i'm not sure you're going to be successful until you change this into a DATE/TIME column | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2009
    Try this:

    SELECT Categories.CategoryName, Activity.CurrDate, Activity.TicketNum, Activity.Duration, Activity.Notes
    FROM Activity INNER JOIN Categories ON Activity.CategoryID = Categories.ID
    WHERE (((Activity.CurrDate)>=Date()-Weekday(Date(),2)+1 And (Activity.CurrDate)<=Date()+7-Weekday(Date(),2)-2))
    ORDER BY Categories.ID, Activity.CurrDate;
    but as r937 said, you need to change CurrDate type to DATE/TIME

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts