help please 2

[kisasama]

I'm really confused since it's about three weeks into using crystal reports and I'm stuck. Here's what's happeneing I'm working on a cobol database with two tables. the first a distributor table and the second is an orders table. I grouped the orders and distributers together and put in a range parameter to suppress any dates (which by the way is in a string) that aren't in the range. Now I want to check for orders made by customers outside that range and compare them with the range. then exclude those cutomers out of the range. A better example of this is having a report that shows a range of orders and customers from jan 01 2002 to dec 31 2003 and exclude anyone customers who have bought in 2004. can this type of thing be done in crystal reports?

tIA

 

[kisasama]

here's what i get when i click on fields in the SQL Expressions Editor.

 

[synapsevampire]

The formula I supplied includes only those under 2004, which is what I understood the requirement for the second data to be.

From what I see of LB's suggestion, this must be more complicated than I understand, it seems to me that the main is fine, simply subreport for less that 2004 and you have those that have not ordered as separate output.

-k

 

[lbass]

Not sure, as I can't test it, but try the following for the SQL expression
{%maxdate}:

(select max(AKA."ENTRY_DATE") from "odh2db" AKA, "dstdb" B where
B."DIST_ID" = "dstdb"."DIST_ID")

If this works, then you would go to report->edit selection formula->RECORD and enter:

{%maxdate} < date(2004,01,01)

-LB

 

[kisasama]

I did everything you said LB accept when i tried to suppress it in the last step i get this error.

 

[kisasama]

inputing the SQL always gives me the error that's

 

[kskid]

How does AKA relate to b? I don't see a join between AKA and b.

Cheers,
-LW

 

[kisasama]

i think the last part with the supression formula should work after we got all the distid's from the subreport. Man.. I really want to get this done. lol thanks guys for all your patience.

 

[kisasama]

so far the last part with the supression formula want a string from the pic above? any idea on what I should do?

 

[lbass]

My mistake--the suppression formula in the section expert should have been:

whileprintingrecords;
shared stringvar distid04;
instr(distid04,totext({dstdb.DIST_ID},0,"")) <> 0

Regarding the SQL expression, you did not incorporate the changes I suggested, so this tells me nothing new. "AKA" and "B" are just alias table names. You are creating a subselect here, and do not need to establish joins here. I believe the syntax is correct, but that your punctuation needs to be adapted as I suggested earlier (although I can't guarantee that since I can't test it).

-LB

 

[kisasama]

thanks lb it worked great. one last question. is there anyway to remove a field if there's nothing in it???

-KS