Date comparison record selection formula

[sumthinsup33]

I am new to Crystal Reports and need to create a record selection formula which compares two different database date fields in a M/D/YYYY HH:MM:SS date format. A record would be returned when {table1.date} >= {table2.date}. Can anyone make any suggestions on how to I can go about this?

An alternative that's been considered is to return a record when the time of the date stamp in {table1.date} is greater than or equal to 08:00 PM. Is this possible?

 

[lbass]

You can just go to report->selection formula->record and enter:

{table1.datetime} >= {table2.datetime}

If you are saying you don't want to take into account the times, then you could use:

date({table1.datetime}) >= date({table2.datetime})

-LB

 

[sumthinsup33]

Thanks. Revisiting the joins helped improve the results but I'm still missing something. After my post, I tested the formula a different way and confirmed that it is correct. There are additional formulas in the selection criteria and the report looked okay until I added the date formula, so I assumed it was the issue but now I know the problem lies elsewhere. Let me explain more fully my requirement to see if you can provide some further insights.

I want to display a record in every instance one of two conditions is met:

1) A new order was entered in the past seven days, {table1.orderdate}, for a particular product, {products.code} = "IUD", or

2) An order change for a particular product, {products.code} = "IUD", has occurred after the order process date, {table1.processdate}, and during the past seven days

Order change records are written to log tables, which contain a transaction date stamps (date & time). There are several of these log tables, one of which is table2. As an example, I am using the transaction date stamp of table2, {table2.changedate}, to limit the report to changes occurring in the past seven days but after the original process date and time, { table1.processdate}.

I need to display the record each time either criterion is met. For example, if a new order was created three days ago but it was changed the next day, the record would appear on the report twice, once in connection with the new order and the second time to show the subsequent update to the order.

Based on these requirements, I’ve written the selection formula below using different variations but none of them work properly. Writing it one way, too many change records from table2 are displayed; changing it other ways displays only the new orders or just those records that have updates. Could you review the syntax below and tell me where I may be going wrong with my logic and how to capture all of the desired records?

currentdate < dateadd('d',7,{table1.orderdate}) and
{products.code} = " IUD" or
currentdate < dateadd('d',7,{ table2.changedate }) and
{table2.changedate} >={ table1.processdate} and
{products.code} = "IUD"

 

[lbass]

You didn't add the necessary parentheses. Also, you will have a faster report if you do the calculation once on the currentdate instead of multiple times on a field in comparison to currentdate. Try the following:

(
(
{table1.orderdate} > currentdate-7 and
trim({products.code}) = "IUD"
) or
(
{table2.changedate} > currentdate-7 and
{table2.changedate} >={table1.processdate} and
trim({products.code}) = "IUD"
)
)

I added trim() around the code field, since you showed it in one place with a leading space and in the other without--this would affect the selection.

-LB