Record Selection Query not working! 1

[willz99ta]

Hi and thanks for your help,

I am trying to use a record selection query to filter out any record lines where {ITEMMAST.USER_FIELD1} = "ACTUAL" -- This is not working. I know it's not working because I have a formula field that is picking up values that are "" <-- these are not null

Here is my record selection query:

if isnull({MMDIST.DOC_TYPE}) Then
(
{PURCHORDER.PO_DATE} in dateserial(year(currentdate),01,01) to dateserial(year(currentdate),12,31) and
{PURCHORDER.PO_DATE} >= cdate({ITEMMAST.USER_FIELD2}) and
{ITEMMAST.USER_FIELD1} = "ACTUAL" and
{PURCHORDER.CANCELLED_FL} = "N" and
{POLINE.COMPANY} = 1000 and
{POLINE.QUANTITY} <> {POLINE.CXL_QTY} and
{PURCHORDER.RELEASED_FL} = "Y"
)
Else
(
{PURCHORDER.PO_DATE} in dateserial(year(currentdate),01,01) to dateserial(year(currentdate),12,31) and
{PURCHORDER.PO_DATE} >= cdate({ITEMMAST.USER_FIELD2}) and
{ITEMMAST.USER_FIELD1} = "ACTUAL" and
{PURCHORDER.CANCELLED_FL} = "N" and
{POLINE.COMPANY} = 1000 and
{POLINE.QUANTITY} <> {POLINE.CXL_QTY} and
{PURCHORDER.RELEASED_FL} = "Y" and
{MMDIST.DOC_TYPE} in ["PT","PA"]
)


Thanks,
Will

 

[Turkbear]

Hi,
The criteria you posted does not seem to be correct

to filter out any record lines where {ITEMMAST.USER_FIELD1} = "ACTUAL" --

It appears that you want those records since you have this:
{ITEMMAST.USER_FIELD1} = "ACTUAL"

Can you expalin more precisely what you want?
( By the way, depending on your database "" can be a NULL).




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[willz99ta]

Whoops, I meant to say that I want only "ACTUAL" records. I have been staring at this report too long.

My only problem is that items other than "ACTUAL" are still showing on my report.


Will

 

[lbass]

I think you should reorganize this to:

{PURCHORDER.PO_DATE} in dateserial(year(currentdate),01,01) to dateserial(year(currentdate),12,31) and
{PURCHORDER.PO_DATE} >= cdate({ITEMMAST.USER_FIELD2}) and
{ITEMMAST.USER_FIELD1} = "ACTUAL" and
{PURCHORDER.CANCELLED_FL} = "N" and
{POLINE.COMPANY} = 1000 and
{POLINE.QUANTITY} <> {POLINE.CXL_QTY} and
(
(
isnull({MMDIST.DOC_TYPE}) and
{PURCHORDER.RELEASED_FL} = "Y"
) or
{MMDIST.DOC_TYPE} in ["PT","PA"]
)

The problem you are seeing in your version is that there are records where doc_type is not null and also not equal to PT or PA, and your if/then allows those other records to be the implied default. If you set it up as shown above, I think you will get the desired record set. You could also set up the last clauses as:

and
(
if isnull({MMDIST.DOC_TYPE}) then
{PURCHORDER.RELEASED_FL} = "Y" else
{MMDIST.DOC_TYPE} in ["PT","PA"]
)

...I just prefer the and/or approach.

-LB

 

[willz99ta]

LBass, I must write a computer poem for you since you saved my bacon once again:

*ahem*

LBass you helped save, my Crystal Reporting junk,
Out of the window, my computer I was going to chunk.

Like the 2001 obelisk, you enlightened my ape-like mind,
I before looked like zombie, groaning wildly - no words could I find

So home I drive, in my crappy Saturn from 02,
You have all thanks, from my whole family to you.


Note: my family was calling me to get home fast

Thanks again,
Will

 

[lbass]

Thanks, Will, for a good laugh--You made my day!

-LB