Date Specific Reporting (97 SR2)

Topic

New Reply

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?

Reply | Quote

Replies

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?

Reply | Quote

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?

Reply | Quote

Drk:

Might this SQL work:

SELECT Count(*) as Count FROM tbl_Data WHERE

and then toss your date criteria in the WHERE clause?

Reply | Quote

An index in Choose can never be zero. Help says [indent]

---

The Choose function returns a Null if index is less than 1 or greater than the number of choices listed.

---

[/indent] 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.

Reply | Quote