Sorted and Unique Date Period

Discussion in 'Microsoft Access Queries' started by CJ, May 22, 2010.

  1. CJ

    CJ Guest

    Hi Groupies

    I had some, much appreciated, assistance putting this Work Period formula
    together. However, now I need to use this Work Period in a form combo to
    filter my reports.

    WorkPeriod: Format(DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket
    Date]),"mmm dd/yy") & " - " & Format(DateAdd("d",-Weekday([Ticket
    Date],6)+7,[Ticket Date]),"mmm dd/yy")

    Since the data is converted to text, sorting puts it in alphabetical, not
    chronological order. Also, I need to see unique values but I can not Group
    it in a query because it is an expression and setting the query Uniqe Value
    property produces an error. I can not sort by the Ticket Date field because
    adding that to the query expression causes duplicate Work Periods.

    To all those with much more knowledge than I, any suggestions?

    --
    Thanks for taking the time!

    CJ
    ---------------------------------------------------------
    Know thyself, know thy limits....know thy newsgroups!
     
    CJ, May 22, 2010
    #1
    1. Advertisements

  2. CJ

    John Spencer Guest

    Why not two dates the start date and the end date?
    Also, if Ticket Date is ever null in ANY record in the query, your expression
    will error on that record. If it errors, that will cause the entire query to
    fail when you try to GROUP, SORT, get Unique records, etc. on that field.


    StartDate: DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date])
    EndDate: DateAdd("d",-Weekday([Ticket Date],6)+7,[Ticket Date])

    Then you can sort by just the start date.

    If you wish to you can still create the WorkPeriod field for display purposes.

    Your row source query might look like the following.
    SELECT Distinct IIF(IsDate([Ticket Date]),Format(DateAdd("d",-Weekday([Ticket
    Date],6)+1,[Ticket Date]),"mmm dd/yy") & " - " &
    Format(DateAdd("d",-Weekday([Ticket Date],6)+7,[Ticket Date]),"mmm
    dd/yy"),Null) as WorkPeriod
    , DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date]) as StartDate
    , DateAdd("d",-Weekday([Ticket Date],6)+7,[Ticket Date]) as EndDate
    FROM [YourTable]
    ORDER BY DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date])

    If you want to use the StartDate and Enddate in queries, you can add two
    controls to your form and set their source to
    =[NameOfCombobox].Column(1)
    and
    =[NameOfCombobox].Column(2)

    Then you can refer to the two controls in the query.

    John Spencer
    Access MVP 2002-2005, 2007-2010
    The Hilltop Institute
    University of Maryland Baltimore County

    CJ wrote:
    > Hi Groupies
    >
    > I had some, much appreciated, assistance putting this Work Period
    > formula together. However, now I need to use this Work Period in a form
    > combo to filter my reports.
    >
    > WorkPeriod: Format(DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket
    > Date]),"mmm dd/yy") & " - " & Format(DateAdd("d",-Weekday([Ticket
    > Date],6)+7,[Ticket Date]),"mmm dd/yy")
    >
    > Since the data is converted to text, sorting puts it in alphabetical,
    > not chronological order. Also, I need to see unique values but I can not
    > Group it in a query because it is an expression and setting the query
    > Uniqe Value property produces an error. I can not sort by the Ticket
    > Date field because adding that to the query expression causes duplicate
    > Work Periods.
    >
    > To all those with much more knowledge than I, any suggestions?
    >
     
    John Spencer, May 22, 2010
    #2
    1. Advertisements

  3. CJ

    CJ Guest

    Thanks John. I thought of the 2 date option but wondered if there was an
    alternative.

    Thanks for the idea of putting the 2 dates into their Work Period formats. I
    hadn't considered doing that. Good Idea!

    --
    Thanks for taking the time!

    CJ
    ---------------------------------------------------------
    Know thyself, know thy limits....know thy newsgroups!
    "John Spencer" <> wrote in message
    news:esUBRWc%...
    > Why not two dates the start date and the end date?
    > Also, if Ticket Date is ever null in ANY record in the query, your
    > expression will error on that record. If it errors, that will cause the
    > entire query to fail when you try to GROUP, SORT, get Unique records, etc.
    > on that field.
    >
    >
    > StartDate: DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date])
    > EndDate: DateAdd("d",-Weekday([Ticket Date],6)+7,[Ticket Date])
    >
    > Then you can sort by just the start date.
    >
    > If you wish to you can still create the WorkPeriod field for display
    > purposes.
    >
    > Your row source query might look like the following.
    > SELECT Distinct IIF(IsDate([Ticket
    > Date]),Format(DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date]),"mmm
    > dd/yy") & " - " & Format(DateAdd("d",-Weekday([Ticket Date],6)+7,[Ticket
    > Date]),"mmm dd/yy"),Null) as WorkPeriod
    > , DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date]) as StartDate
    > , DateAdd("d",-Weekday([Ticket Date],6)+7,[Ticket Date]) as EndDate
    > FROM [YourTable]
    > ORDER BY DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date])
    >
    > If you want to use the StartDate and Enddate in queries, you can add two
    > controls to your form and set their source to
    > =[NameOfCombobox].Column(1)
    > and
    > =[NameOfCombobox].Column(2)
    >
    > Then you can refer to the two controls in the query.
    >
    > John Spencer
    > Access MVP 2002-2005, 2007-2010
    > The Hilltop Institute
    > University of Maryland Baltimore County
    >
    > CJ wrote:
    >> Hi Groupies
    >>
    >> I had some, much appreciated, assistance putting this Work Period formula
    >> together. However, now I need to use this Work Period in a form combo to
    >> filter my reports.
    >>
    >> WorkPeriod: Format(DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket
    >> Date]),"mmm dd/yy") & " - " & Format(DateAdd("d",-Weekday([Ticket
    >> Date],6)+7,[Ticket Date]),"mmm dd/yy")
    >>
    >> Since the data is converted to text, sorting puts it in alphabetical, not
    >> chronological order. Also, I need to see unique values but I can not
    >> Group it in a query because it is an expression and setting the query
    >> Uniqe Value property produces an error. I can not sort by the Ticket Date
    >> field because adding that to the query expression causes duplicate Work
    >> Periods.
    >>
    >> To all those with much more knowledge than I, any suggestions?
    >>
     
    CJ, May 23, 2010
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Lucky

    Convert 2 week period to weekly period

    Lucky, Oct 30, 2003, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    291
    Lucky
    Oct 31, 2003
  2. Miaplacidus

    Unique Values vs Unique Records

    Miaplacidus, Sep 17, 2004, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    747
  3. Guest

    finding if a period falls within a period in access 2000

    Guest, Oct 18, 2005, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    274
    Michel Walsh
    Oct 18, 2005
  4. Lance
    Replies:
    3
    Views:
    265
    Lance
    May 9, 2006
  5. Guest

    Unique Values v Unique Records

    Guest, Feb 5, 2007, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    4,753
    Guest
    Feb 5, 2007
Loading...

Share This Page