Hi,
I am using CR 8.5, pervasive db for ACCPAC. I'm trying to write a report which outputs a bunch of stuff. Basically, there are two tables which contain the data i need, and my client has a report to access these tables but it's too slow because it uses a subreport whenever it need data from the second table. I am converting it to use left outer joins instead.
My problem is this:
My selection formula looks something like this:
( LOTS OF STATEMENTS BLAH BLAH BLAH ... )
AND
(
(
{GLPOSTO_WORKCODE.OPTFIELD} = "WORKCODE" and
{GLPOSTO_SITE.OPTFIELD} = "SITE" and
{GLPOSTO_STATE.OPTFIELD} = "STATE" and
{GLPOSTO_DATEINCURRED.OPTFIELD} = "DATEINCURRED"
)
OR
(
isNull({GLPOSTO_WORKCODE.OPTFIELD}) and
isNull({GLPOSTO_SITE.OPTFIELD}) and
isNull({GLPOSTO_STATE.OPTFIELD}) and
isNull({GLPOSTO_DATEINCURRED.OPTFIELD})
)
)
That is pretty much what I want. However, I can't figure out why it is not working. This selection formula will output, for example, 700 records. I know this number is wrong because the original report will output, for example, 800 records. If I write:
( LOTS OF STATEMENTS BLAH BLAH BLAH ... )
AND
(
{GLPOSTO_WORKCODE.OPTFIELD} = "WORKCODE" and
{GLPOSTO_SITE.OPTFIELD} = "SITE" and
{GLPOSTO_STATE.OPTFIELD} = "STATE" and
{GLPOSTO_DATEINCURRED.OPTFIELD} = "DATEINCURRED"
)
I will get 700 records.
If I write:
( LOTS OF STATEMENTS BLAH BLAH BLAH ... )
AND
(
isNull({GLPOSTO_WORKCODE.OPTFIELD}) and
isNull({GLPOSTO_SITE.OPTFIELD}) and
isNull({GLPOSTO_STATE.OPTFIELD}) and
isNull({GLPOSTO_DATEINCURRED.OPTFIELD})
)
I will get the other hundred records I am expecting. Is there something fundamentally wrong that I can't see? Thanks.
-Victor
I am using CR 8.5, pervasive db for ACCPAC. I'm trying to write a report which outputs a bunch of stuff. Basically, there are two tables which contain the data i need, and my client has a report to access these tables but it's too slow because it uses a subreport whenever it need data from the second table. I am converting it to use left outer joins instead.
My problem is this:
My selection formula looks something like this:
( LOTS OF STATEMENTS BLAH BLAH BLAH ... )
AND
(
(
{GLPOSTO_WORKCODE.OPTFIELD} = "WORKCODE" and
{GLPOSTO_SITE.OPTFIELD} = "SITE" and
{GLPOSTO_STATE.OPTFIELD} = "STATE" and
{GLPOSTO_DATEINCURRED.OPTFIELD} = "DATEINCURRED"
)
OR
(
isNull({GLPOSTO_WORKCODE.OPTFIELD}) and
isNull({GLPOSTO_SITE.OPTFIELD}) and
isNull({GLPOSTO_STATE.OPTFIELD}) and
isNull({GLPOSTO_DATEINCURRED.OPTFIELD})
)
)
That is pretty much what I want. However, I can't figure out why it is not working. This selection formula will output, for example, 700 records. I know this number is wrong because the original report will output, for example, 800 records. If I write:
( LOTS OF STATEMENTS BLAH BLAH BLAH ... )
AND
(
{GLPOSTO_WORKCODE.OPTFIELD} = "WORKCODE" and
{GLPOSTO_SITE.OPTFIELD} = "SITE" and
{GLPOSTO_STATE.OPTFIELD} = "STATE" and
{GLPOSTO_DATEINCURRED.OPTFIELD} = "DATEINCURRED"
)
I will get 700 records.
If I write:
( LOTS OF STATEMENTS BLAH BLAH BLAH ... )
AND
(
isNull({GLPOSTO_WORKCODE.OPTFIELD}) and
isNull({GLPOSTO_SITE.OPTFIELD}) and
isNull({GLPOSTO_STATE.OPTFIELD}) and
isNull({GLPOSTO_DATEINCURRED.OPTFIELD})
)
I will get the other hundred records I am expecting. Is there something fundamentally wrong that I can't see? Thanks.
-Victor