Date Comparison

[metalteck]

In my 8.5 version of crystal, I'm trying to compare 2 dates. In the database, the fields tran and svc date are stored as date1,date2,date3. Date1 = month, date2 = day, date3 = year.

My formula is this:
If transdate > svcdate then "after"
else "before"

My problem is that it requires me to put a set of quotation marks before svcdate and does not let me check for any records the records that may in the before category. Could you help me on this problem.

Thank you

 

[synapsevampire]

You need to post technical information.

What is the data type of the 3 fields that comprise these 2 date fields? You can right click each and select browse data and Crystal will tell you.

And read this through and rewrite it so that it makes sense: "and does not let me check for any records the records that may in the before category."

It's probably best to supply:

Database/connectivity used
Example data
Expected output

-k

 

[metalteck]

The 3 fields that make up the 2 date fields are number types.

Right now the query looks like:

TransDate SvcDate
3/10/2006 4/10/2006
4/15/2006 1/21/2006

I want the report to be able to look up through the dates and only pick the dates where transdate is greater than svcdate.

 

[synapsevampire]

OK, this should work:

Go to Report->Selection Formulas->Record and place:

cdate({table.transdateyear},{table.transdatemonth},{table.transdateday}) >
cdate({table.svcdateyear},{table.svcdatemonth},{table.svcdateday})

I can't understand why your posted results would show the data as dates if they are broken apart as numbers though, thi is confusing, and what you are showing is not a query, it's some sample data (I guess), queries are select statements.

-k