[Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2006-03-21
Medium Priority
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

Kurt Reinhardt earned 1600 total points
ID: 16247594
You can simply use:

(CurrentDate - 1)


Expert Comment

ID: 16247626
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

ID: 16247673
Yes - I am using select expert

Expert Comment

ID: 16247750
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

bdreed35 earned 400 total points
ID: 16247827
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:

{table.date} = 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month20 days, 14 hours left to enroll

810 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