Record Selection Problem and IsNull formula 1

[dcanfield]

Record Selection Problem and IsNull formula

I have a report that I am trying to create, but am not having any luck getting the Record Selection formula to work properly.

The scenario (CR10 – Access Database) :

My company has several different divisions, and each division sells the same products. I am trying to get a report that will show the outstanding Accounts Receivable balance for each product sold for each division, broken down by date range of how old the receivable balance is.





For example, for Division A, there a number of Product Codes (PC)
PC A
PC B
PC C
PC D
PC E
PC F
PC G
..
..
..
PC X
PC Y
PC Z

The first table (T_MASTER_ATB ) is the table that is a listing of all outstanding unpaid sales, and has the following fields:
FACILITY
DAYS
ATB DATE
BALANCE
PC


The other table (T_MASTER_PC_LIST) is a listing of all of the Divisions and Product Codes (PC). That table has just two fields
FACILITY
PC

The two tables are joined with a LEFT JOIN, with the T_MASTER_PC_LIST on the left, and the T_MASTER_ATB on the right.



I use a record selection formula as shown:

{T_MASTER_ATB.BALANCE} >= $0.00 and
{T_MASTER_ATB.ATB DATE} = 04/01/05 and
{T_MASTER_ATB.DAYS} in 91 to 120 and
{T_MASTER_PC_LIST.FACILTIY} = "DIVISION A" and
({T_MASTER_PC_LIST.PC}= "B" or
{T_MASTER_PC_LIST.PC = "E" or
{T_MASTER_PC_LIST.PC = "F" or
{T_MASTER_PC_LIST.PC = "I" or
{T_MASTER_PC_LIST.PC = "J" or
{T_MASTER_PC_LIST.PC = "K" or
{T_MASTER_PC_LIST.PC = "L" or
{T_MASTER_PC_LIST.PC = "M" or
{T_MASTER_PC_LIST.PC = "O" or
{T_MASTER_PC_LIST.PC = "P" or
{T_MASTER_PC_LIST.PC = "Q" or
{T_MASTER_PC_LIST.PC = "T" or
{T_MASTER_PC_LIST.PC = "U" or
{T_MASTER_PC_LIST.PC = "W" or
{T_MASTER_PC_LIST.PC = "X" or
{T_MASTER_PC_LIST.PC = "Y" or
{T_MASTER_PC_LIST.PC = "Z")



In my report, for PC=X, there are no records that match my selection criteria.

When I use this selection criteria, it works find, and displays the correct records, showing all outstanding balances as expected. But for PC=X, they just show a blank, and all PC’s under it move up on the report display.

For PC’s that do not have any records that match this criteria, I want it to show a 0.00.

I have created an IsNull formula to try to display the NULL PC=X as 0.00, as shown here:

If IsNull (Sum ({T_MASTER_ATB.BALANCE},{T_MASTER_PC_LIST.FC}))
then 0.00
else
(Sum ({T_MASTER_ATB.BALANCE},{T_MASTER_PC_LIST.PC}))

This formula works as long as I do NOT use any record selection formula, and just report on the entire database. But as soon as I use the above Record Selection formula, the IsNull formula does NOT work, and nothing is displayed for PC=X.

In summary….If I use Record Selection, the IsNull formula does not work. If I do NOT use Record Selection, the IsNull formula works, but the entire database is reported, not just the records that I want to see.

Any thought, suggestions are appreciated.

Regards,
David

 

[synapsevampire]

You have criteria on the child table, so you are specifically stating that you want ONLY those rows which have:

{T_MASTER_ATB.BALANCE} >= $0.00 and
{T_MASTER_ATB.ATB DATE} = 04/01/05 and
{T_MASTER_ATB.DAYS} in 91 to 120

Hence you won't get the PC X rows because they don't match.

You might try something like:

(
{T_MASTER_ATB.BALANCE} >= $0.00 and
{T_MASTER_ATB.ATB DATE} = 04/01/05 and
{T_MASTER_ATB.DAYS} in 91 to 120
)
or
(
isnull({T_MASTER_ATB.BALANCE}) and
isnull({T_MASTER_ATB.ATB DATE}) and
isnull({T_MASTER_ATB.DAYS})
)
and
...

An alternative would be to use a subreport, however your performance will degrade significantly.

-k

 

[dcanfield]

Hi synapsevampire,

thanks for the feedback....

I tried this, but for PC= X, there is still no results from my IsNull formula....still blank.

And just as an additional bit of info, I have made sure the "Suppress Zero" is turned OFF in field format, and in Report Options, the "Convert Database NULL Values to Default" is NOT checked.

Any other ideas?

Thanks again!

David

 

[Madawc]

Split the formual into separate chunks. Display them as formula fields, with the record selection commented out. Find out which bit isn't working as you expected.

If you create formual fields without an 'If', you'll get a Boolian, which should be 'True' or 'False'. And will be blank only if the data contained an unexpected null.

Madawc Williams (East Anglia, UK)

 

[Madawc]

I tend to build up my reports a chunk at a time, rather than defining a grand formula that should do everything. A boolian formual field can be tested-for in Report Selection, just by citing a name like @Bigtest, or not @Bigtest for those outside of the criteria.

Madawc Williams (East Anglia, UK)

 

[synapsevampire]

This should have read:

(
(
{T_MASTER_ATB.BALANCE} >= $0.00 and
{T_MASTER_ATB.ATB DATE} = 04/01/05 and
{T_MASTER_ATB.DAYS} in 91 to 120
)
or
(
isnull({T_MASTER_ATB.BALANCE}) and
isnull({T_MASTER_ATB.ATB DATE}) and
isnull({T_MASTER_ATB.DAYS})
)
)
and
...

That should handle it correctly, but check the Database->Show SQL Query

-k