Query based on date

In the Report -> Selection Formulas type:

{PurchaseOrderTable.PurchaseOrderDate} in CurrentDate-365 to CurrentDate

Are you using ODBC connection? If using ODBC, you can add something like this to the WHERE clause of your select statement:

WHERE PO_DATE > SYSDATE - 365.

Arvin Ojales

Depending on how you run your reports (if you use LBI or Enterprise for example where there are historical instances) CurrentDate and DataDate can produce very different results. I'd generally recommend using datadate and the record selection would be {PurchaseOrderTable.PurchaseOrderDate} in Date(year(datadate)-1, month(datadate), day(datadate)) to DataDate - this will also assure correct results for leap year

Char,

Please explain the difference between CurrentDate and DataDate.

Thank you!

DataDate is the date the query executed against the database whereas CurrentDate is the date you're looking at the report. Let's say for Example that you have a formula that says "if {table.field} = currentdate -1 then {table.amount}" and you run the report on Monday thereby creating a historical instance. If you go and look at that historical instance on Friday then the formula will try to display data for Thursday since it's the day before Friday and there will be none since you ran the report on Monday and you'll see 0. If the formula had used DataDate then the numbers stay consistent over time because you're always looking at that fixed point in time that is the date the query executed. This same type of issue can arise when you use certain functions such as LastFullMonth because they are based internally on the currentdate rather than than the datadate. I hope that's clear......

THANK YOU CHAR!!!

Very clear explanation. Guess there are some reports I need to rewrite!

I'm connecting via OLE DB. It looks like the solution is found after the query, within the report itself, letting Crystal do the work?

Thanks Char! Your formula worked beautifully!