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

    Date Specific Reporting (97 SR2)

    I have a formula which runs between two dates, the first of these dates is:

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

    Currently, this returns 8/29/01.

    I would like to report on the number of entries made before this date. I have a field called 'DateStarted' but can't get anything to count every entry made before this date.

    Any ideas?
    <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
    Sep 2001
    Location
    SC, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Specific Reporting (97 SR2)

    Seems like you could do a count on all records, but in criteria use this: "[Datestarted]<[Other Date field]". Maybe I'm misunderstanding your question. Can you provide a little more info?

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

    Re: Date Specific Reporting (97 SR2)

    I was thinking this would work:

    =DCount("*","tblMain","[datestarted]<Date()-Choose(DatePart("w",Date()),4,5,6,0,1,2,3)-7")

    but it tells me that's invalid syntax...

    Any ideas?
    <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
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Specific Reporting (97 SR2)

    Drk:

    Might this SQL work:

    SELECT Count(*) as Count FROM tbl_Data WHERE

    and then toss your date criteria in the WHERE clause?
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Specific Reporting (97 SR2)

    An index in Choose can never be zero. Help says
    <hr>The Choose function returns a Null if index is less than 1 or greater than the number of choices listed.<hr>
    There is also no reason to put them in an odd order, since they all get evaluated every time and it's easier to read if they're sequential.

    You're probably getting the invalid syntax because of the double quotes aroung "w". Try changing them to single quotes.
    Charlotte

Posting Permissions

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