No records returned from a correct formula

[markchimes]

I am attempting to produce a cross-tab report from a membership DBF file that counts the number of members that are Inactive belonging to a particular District for a particular Financial Year that do not have Mail Status Flags of 'D' or 'T'.
When using the Crystal Report Expert and selecting the includes and excludes from the Select Tab, the formula that is produced does not return any records.

I have checked the formula with a separate application which returns the correct number of members (ie: 123).

I notice that when I click Preview or refresh the report data, Crystal only counts through the number of records that match the District selected (eg: 9,000 for District A, 12,000 for District S, etc). If I remove the District variable from the criteria in the formula, Crystal will count through the whole DBF file. The DBF file contains approximately 80,000 records.

The formula produced by the Report Expert is:
{MEMBERS.TAG} = "I" and
not {MEMBERS.MAILFLAG} in ["D", "T"] and
{MEMBERS.DISTRICT} = {?DistrictCode} and
{MEMBERS.FINANCIAL} = {?FinancialYear}

If I modify this formula to include parenthesis, no difference is made.
eg:
({MEMBERS.TAG} = "I" and not ({MEMBERS.MAILFLAG} in ["D", "T"])) and
({MEMBERS.DISTRICT} = {?DistrictCode} and {MEMBERS.FINANCIAL} = {?FinancialYear})

Thanks for your help.

cheers,
Mark Chimes

 

[chelseatech]

See if NULLs are causing you a problem...
File/Report Options/Convert NULLS to default.
Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[markchimes]

I have this option ticked by default. Some of the fields in the election criteria may be blank/empty but I have assumed that this would mean the record falls outside the scope I am trying to check for.

cheers,
Mark Chimes

 

[Ngolem]

Hi
{MEMBERS.TAG} = "I" and
not {MEMBERS.MAILFLAG} in ["D", "T"] and
{MEMBERS.DISTRICT} = {?DistrictCode} and
{MEMBERS.FINANCIAL} = {?FinancialYear}

is it possible that the parameter,{?FinancialYear} is in the wrong format??

Go to the Record Selction formula and selectively remove a filter until you get records...then you will find the bad guy causing the problem

do this by placing // infront of the filter you want to remove...eg:

{MEMBERS.TAG} = "I" and
//not {MEMBERS.MAILFLAG} in ["D", "T"] and
{MEMBERS.DISTRICT} = {?DistrictCode} and
{MEMBERS.FINANCIAL} = {?FinancialYear}

will remove the filter for {MEMBERS.MAILFLAG} ... if records are returned...add this field to the report to see why it is not being filtered...do this for all filters


Jim
JimBroadbent@Hotmail.com