Date Formula not returning correct value 1

[Cort]

Hmm in CR 8.5 here is my formula I was to see if the contract was closed during July, 2000. Any reason this isn't working?

IF MONTH({dbo_CRTR_CURR.CLOSED_DATE}) = 7 AND YEAR({dbo_CRTR_CURR.CLOSED_DATE}) = 2002 THEN 1 ELSE 0

I then use a running total to sum all the 1's. I'm getting a number far too large to be correct as my answer.

Thank You

 

[synapsevampire]

The year in your formula is 2002.

Also, this probably isn't passed to the database for processing, so it runs more slowly.

Try:

{dbo_CRTR_CURR.CLOSED_DATE} >= date('6/1/2000')
and
{dbo_CRTR_CURR.CLOSED_DATE} <= date('6/1/2000')

You might even consider creating a date range parameter to prompt for the dates used so that the report is more flexible.

In that case you'd use:

{dbo_CRTR_CURR.CLOSED_DATE} = {?mydates}

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Cort]

Thanks SV

{dbo_CRTR_CURR.CLOSED_DATE} >= date('7/1/2002')
and
{dbo_CRTR_CURR.CLOSED_DATE} < date('8/1/2002')

worked fine.

Any idea why my formula didn't work?

 

[kenhamady]

Your original formula should at least give you the correct number (assuming you used the correct year). Why did you use a running total. A regular total would also have worked. Did you use a running total formula? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Cort]

I've become somewhat addicted to running totals actually. I find them easy to use and manipulate.

The reason I used a RT on this particular form is that I have a running total for each month of the last 24 months. The formula in the details section looks to see if a transaction happened in that particular month and the places a 1 or 0 which is summed by the RT. A simply summary would probably have worked just as well but I've gotten into a habit of using running totals in anything using 2 or more groups.

I have modified the formula a small about to list the [contract number] if true and a &quot;0&quot; if false and then used a dictinct count running total with the record selection formula to not count and &quot;0&quot; returns.

The problem with me working in this particular accounting table is 1 part bad table design on the part of accounting support and 1 part inadequate knowledge on my part. The report (which I have finally finished) has no less that 192 seperate formulas and running totals. It works but it slow and took a very long time to design and get working. If I had a much stronger knowledge of the more advanced functions of Crystal I could have done the report in a fraction of the time using dynamic arrays I bet. I hope to locate a good seminar on CR in the area.

Again, I very much appreciate the excellant help given in this forum. You folks are a lifesaver for donating your time.