Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Criteria (97 SR2)

    This may be a no-brainer for you Access Gurus, but here's what I want to do:

    A query is run against a set of data. This data contains a field for 'Date'. I want to display only the data that was generated between Friday and Friday. So today, being Monday, it would only show records generated from the 3rd of august, to the 10th. (Friday to Friday). If I run it on Thursday, it will show the same data set. If I run it on Friday, it will show the records from the 10th to the 17th.

    Ideally this will be automatic, but if it's not possible, I can force the user to specify dates..

    Is this possible?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Star Lounger
    Join Date
    Apr 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (97 SR2)

    Maybe not the most eloquent formula, but it's a start. Try the following:

    In the query - for the criteria row of the field that has the date, type in the following:

    Between ((Date()-Choose(DatePart("w",Date()),2,3,4,5,6,7,0))-7) And (Date()-Choose(DatePart("w",Date()),2,3,4,5,6,7,0))

    NOTE: This is as you requested in your posting. However, this will actually include 8 days worth of data. If you're only interested in one week's worth of data you'd need to decide which end of the formula to deduct a day from.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (97 SR2)

    Thanks TJ!

    Say, what part of this formula is determining that the data retrieved is done so on Friday? I wouldn't be surprised if my boss decided that Monday would be better...

    Thanks again!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (97 SR2)

    Anyone?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (97 SR2)

    Sorry I didn't answer sooner, but I didn't get back to the board until today.

    ==============
    Original Formula
    ==============
    Between ((Date()-Choose(DatePart("w",Date()),2,3,4,5,6,7,0))-7) And (Date()-Choose(DatePart("w",Date()),2,3,4,5,6,7,0))

    ==============
    Explanation
    ==============
    The DatePart("w",Date()) of the formula returns an integer indicating which day of the week the date is, 1=Sunday through 7=Saturday.

    The Choose() portion returns how many days to subtract from the current Date(). The number of days is determined by the integer returned from the DatePart() function.

    For Example: If the date is a Sunday, DatePart() returns a 1. This then causes Choose() to select the first item in the list of 2,3,4,5,6,7,0.

    So, to adjust the formula to use for Monday vs. Friday you'd need to adjust the Choose() select list to:
    6,0,1,2,3,4,5

    So the revised formula for Monday's would be:

    ==============
    Revised Formula
    ==============
    Between ((Date()-Choose(DatePart("w",Date()),6,0,1,2,3,4,5))-7) And (Date()-Choose(DatePart("w",Date()),6,0,1,2,3,4,5))


    HTH

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (97 SR2)

    thank you kindly! <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  7. #7
    Star Lounger
    Join Date
    Apr 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (97 SR2)

    You are quite welcome. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I'm still waiting to see though if there someone else has a cleaner method.

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (97 SR2)

    Alas, I was right.. Wed to Wed.. The "0" in your formula is confusing me... <img src=/S/help.gif border=0 alt=help width=23 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  9. #9
    Star Lounger
    Join Date
    Apr 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (97 SR2)

    OOOPS. <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    The reason the zero was probably confusing you is I discovered it (the zero) was misplaced in the initial formula. The zero should be located in the Choose() list for the # related to the day of the week. So, if you wanted to work with Sunday-Sunday, since Sunday equates to the integer 1, the zero should be in the first position. For Wednesdays, the zero should be in the 4th position, i.e.

    Date()-Choose(DatePart("w",Date()),4,5,6,0,1,2,3)

    Clear as mudd?

  10. #10
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (97 SR2)

    I use the statement
    endofweek(DateAdd("d",Date(),-7),7)
    as the criteria for the date field.

    endofweek is a function that returns the date representing the last day of the current week.


    Function EndOfWeek(D As Variant, Optional FirstWeekday As Integer) As Variant
    '
    ' Returns the date representing the last day of the current week.
    '
    ' Arguments:
    ' D = Date
    ' FirstWeekday = (Optional argument) Integer that represents the first
    ' day of the week (e.g., 1=Sun..7=Sat).
    '
    If IsMissing(FirstWeekday) Then 'Sunday is the assumed first day of week.
    EndOfWeek = D - WeekDay(D) + 7
    Else
    EndOfWeek = D - WeekDay(D, FirstWeekday) + 7
    End If
    End Function

Posting Permissions

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