Dont filter out Null value in Selection Criteria

[Former Member]

Hi

I created a crystal report that prompt user enter a salesrep's customer list that show last day the customer brought something;

Problem:

on my selection criteria i have:

1)salesrep number

2)order delete flag = N (i want to filter out any order that is been deleted

3)order cancel flag = N

above selection criteria filters out all my customer with delete flag = N but also customer with Null value, which i dont want my NULL to be filter out; i want to show all customer either brought not brought;

please help

[Former Member · Accepted solution]

Try this in record selection

order delete flag = N and order cancel flag = N and isnull(field)=true

Also in the record select expert select the option "Default values for nulls"

Regards,

Raghavendra

[Former Member · Accepted solution]

i try that it didnt' work; it elimate all my result;

does anybody know why this is not working in my selection criteria?

{p21_view_customer.salesrep_id} = {?PLEASE ENTER SAELSREP NUMBER} and

({p21_view_oe_hdr.delete_flag} = "N" or

isnull({p21_view_oe_hdr.delete_flag})=true)

when i look at SQL statment; the isnull part is not included;

below is my SQL statement show in crystal:

SELECT "p21_view_customer"."customer_name",

"p21_view_customer"."customer_id",

"p21_view_address"."phys_address1",

"p21_view_address"."phys_city",

"p21_view_address"."phys_state",

"p21_view_ship_to"."ship_to_id",

"p21_view_customer"."salesrep_id",

"p21_view_contacts"."first_name",

"p21_view_contacts"."last_name",

"p21_view_address"."phys_postal_code",

"p21_view_address"."central_phone_number",

"p21_view_oe_hdr"."order_date",

"p21_view_oe_hdr"."order_no",

"p21_view_oe_hdr"."cancel_flag",

"p21_view_oe_hdr"."delete_flag",

"p21_view_oe_hdr"."rma_flag"

FROM "P21Report"."dbo"."p21_view_address" "p21_view_address"

INNER JOIN (((("P21Report"."dbo"."p21_view_oe_hdr" "p21_view_oe_hdr"

FULL OUTER JOIN "P21Report"."dbo"."p21_view_customer" "p21_view_customer"

ON "p21_view_oe_hdr"."customer_id" = "p21_view_customer"."customer_id")

INNER JOIN "P21Report"."dbo"."p21_view_contacts" "p21_view_contacts"

ON "p21_view_customer"."salesrep_id" = "p21_view_contacts"."id")

FULL OUTER JOIN "P21Report"."dbo"."p21_view_ship_to" "p21_view_ship_to"

ON "p21_view_customer"."customer_id" = "p21_view_ship_to"."customer_id")

FULL OUTER JOIN "P21Report"."dbo"."p21_view_ship_to_salesrep" "p21_view_ship_to_salesrep"

ON "p21_view_ship_to"."ship_to_id" = "p21_view_ship_to_salesrep"."ship_to_id")

ON "p21_view_address"."id" = "p21_view_ship_to_salesrep"."ship_to_id"

WHERE "p21_view_customer"."salesrep_id" = '1070'

ORDER BY "p21_view_ship_to"."ship_to_id",

"p21_view_customer"."salesrep_id",

"p21_view_customer"."customer_id"

[Former Member · Accepted solution]

Please try this and let me know the result

{p21_view_customer.salesrep_id} = {?PLEASE ENTER SAELSREP NUMBER} and

({p21_view_oe_hdr.delete_flag} = "N" or

isnull({p21_view_oe_hdr.delete_flag})=true or

trim({p21_view_oe_hdr.delete_flag})="")

Regards,

Raghavendra

[Former Member · Accepted solution]

OH MY GOD!

[Former Member · Accepted solution]

Trim function removes spaces in the field. I think the field values are not null they are filled with spaces