We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

glabossi
glabossi asked
on
Medium Priority
1,165 Views
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.

Gilbert
Comment
Watch Question

Sr. Business Intelligence Consultant/Architect
Commented:
You can simply use:

(CurrentDate - 1)

~Kurt

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
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.

dylan

Author

Commented:
Yes - I am using select expert

Commented:
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)

dylan
Top Expert 2004
Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.