Select Current Month

[lauriebizz]

Hi - easy question... I'm using Crystal IX and SQL

Is there a formula to select records with a due date of the current month?

Thanks-
Laurie

 

[synapsevampire]

Report->Selection Formulas->Record

{table.duedate} in monthtodate

-k

 

[lauriebizz]

Thank You!! I knew it was painfully easy.

Thanks-
Laurie

 

[synapsevampire]

Didn't sting that much, did it?

There are a host of standard date ranges in Crystal, including yeartodate, lastfullmonth, etc.

-k

 

[lauriebizz]

Ack... that's not it. I need the whole current month, not month to date.
Thanks-
Laurie

 

[lauriebizz]

Got it!!!!

month({coitem.due_date}) = month (today) and
year({coitem.due_date}) = year (today)

 

[synapsevampire]

<laffin>

OK, so you want to include dates beyond today, try:

{table.duedate} >= minimum(monthtodate)
and
{table.duedate} <= dateserial(year(monthtodate),month(monthtodate)+1,1)-1

That will return data for any dates that match the current month.

-k

 

[rosemaryl]

Dear Laurie,

Just to double check, you need to automate the running of the report so that it always returns any records where the due date is in the current month, is that correct?

If so, that is also easy:

{table.duedate} in
CDATE(datepart('yyyy',currentdate),DatePart('m',currentdate),01)
to
CDATE(Dateadd('d',-1,cdate(datepart('yyyy',currentdate),datepart('m',currentdate)+1,01)))

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[rosemaryl]

Dear Laurie,

When you go to try and pass the record selection criteria to the SQL Where Clause you find that functions like month and year will not pass.

Better options are SV's formula (nicely done and simpler than mine) or mine.

Both CDATE and DateSerial will pass to the database resulting in faster performance.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[lauriebizz]

Thanks for all the other options. I'll try them tomorrow and see which works best for my application.
Thanks again for all your help and snickers

I always look forward to responses.
Thanks-
Laurie

 

[synapsevampire]

The snicker was because it turned out to be slightly painful

It doesn't matter whether you use Ro's or my formula, just don't use yours, it'll prove slower.

-k

 

[lauriebizz]

haha - at least it works! I bet if someone took at look at my "programming" they would more than snicker!


Laurie

 

[synapsevampire]

Well you're doing the right thing, use whatever resources are available, most coders spend their bandwidth trying to figure things out. Pretty much everything has already been done, stick with copy-n-paste whenever you can.

-k