How do I set up a date formula to pull yesterday's date's worth of data automatically?

Posted on 2006-03-21
Last Modified: 2008-02-01
How do I set up a date formula to pull yesterday's date's worth of data automatically?

I see that they have a CurrentDate but nothing for yesterday's date.  I need to set it up so it runs automatically and this is a formula that I need to put in.

Thanks for any help you can give.

Question by:glabossi
    LVL 26

    Accepted Solution

    You can simply use:

    (CurrentDate - 1)

    LVL 8

    Expert Comment

    are you using select expert to filter records? You could use dateadd to get yesterday's date, eg)
             dateadd("day", -1, CurrentDate)

    In your select expert, use the above syntax to filter your records.


    Author Comment

    Yes - I am using select expert
    LVL 8

    Expert Comment

    Do you store time in your datetime field? if you don't, you can simply set the filter like:
    {table.datefield} = dateadd("day", -1, CurrentDate)   // or kurt's (CurrentDate -1)

    if you datetime field is storing time information, then you could use:
    ({table.datefield} >= dateadd("day", -1, CurrentDate) AND {table.datefield} < CurrentDate)

    LVL 28

    Assisted Solution

    You will find it is much easier to enter this right into the Formula Editor for the Record Selection, which is what the Select Expert does for you.

    Report menu, Selection Formulas, Record.
    You can then enter in your formula in the editor there.
    Regardless if your field is a date or a datetime, you can use this:

    {} = currentdate - 1

    Crystal will translate it into the WHERE clause of your SQL with a time stamp if it is a date time or without a timestamp it is just a date.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now