How to tell if a field is really blank

[wanzek]

I am working on a report that has certain locations for all of our equipment. For example:

YARD
YARD2
CORP
AVA
SOLD
JUNK
X-KM

My problem is that I am trying to condition the report to only print some of the locations. I would like to only see the SOLD, UNK, X-KM, JUNK and the blank locations. I have tried the following in my selection formula:
{EMEM.Status}='A' and
{EMEM.Location}= 'SOLD' or
{EMEM.Location}= 'UNK' or
{EMEM.Location}= 'X-KM' or
{EMEM.Location}='JUNK' or
{EMEM.Location}=''

It only returns X-KM, UNK, JUNK. Is there something wrong in my formula?

 

[jmd0252]

depending on the database the value of location,, could be "null",, and null values can be handled in different ways,, you can look at the database,, and see is the field says "null",, and then go from there..

 

[GJParker]

Try this the location test needs to be in brackets so that it is done as one test

{EMEM.Status}='A' and
(
IsNull({EMEM.Location} or
{EMEM.Location} = 'SOLD' or
{EMEM.Location} = 'UNK' or
{EMEM.Location} = 'X-KM' or
{EMEM.Location} ='JUNK' or
{EMEM.Location} =''
)

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[Turkbear]

Hi,
To insure that fields with spaces get handled, try this mod of GJParker's formula:

{EMEM.Status}='A' and
(
IsNull({EMEM.Location} or
{EMEM.Location} = 'SOLD' or
{EMEM.Location} = 'UNK' or
{EMEM.Location} = 'X-KM' or
{EMEM.Location} ='JUNK' or
TRIM({EMEM.Location}) =''
)

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

 

[wanzek]

Neither of the suggested formulas work.

When I use brackets the formula doesn't work at all.

Any idea why this would be?

 

[wanzek]

Maybe I am going about this the wrong way. I have 3 columns of data. The unit #, the current job #, and the location. For example
Unit # Current Job # Location
10104 677 AVA
10110 X-KM
10280
10290 597

What I need to do with the above data is if the Unit # has both a current job & a location don't print. If the Unit # has just a location that is (UNK, SOLD, X-KM, JUNK) to print. If the Unit # has no current job or location to print. If the unit has just a job don't print.

 

[fisheromacse]

try this in the select expert:

(
((isnull({EMEM.location}) or trim({EMEM.location})="") and
(isnull({table.job}) or trim({table.job})="")
OR
({EMEM.location} in [unk,sold,x-km,junk] and (isnull({table.job}) or trim(totext({table.job}))=""))
)

 

[wanzek]

Using this formula:
(
((isnull({EMEM.location}) or trim({EMEM.location})="") and
(isnull({EMEM.Job}) or trim({EMEM.Job})="")
OR
({EMEM.location} in ['unk','sold','x-km','junk'] and (isnull({EMEM.Job}) or trim(totext({EMEM.Job}))=""))
) )
They only thing I get are those that have no current job or location.

 

[jmd0252]

since you have tried everything else,, change your last line ito say.
if isnull(FIELD NAME HERE),, and see what that does..

 

[wanzek]

I'm sorry. Which part should I change?

 

[Turkbear]

Hi,
OK, one more thing, in CR the NULL test should be the first thing in the selection formula so try:

(
(IsNull({EMEM.Location}) or Trim({EMEM.Location}) = "")
AND
IsNull({Job})
) [COLOR=green]// Has No Job and No Location[/color]

OR

(
IsNull({Job})
AND
{EMEM.Location} In ['SOLD','UNK','X-KM','JUNK']
) [COLOR=green] // Has Locations wanted and NO Job [/color]

OR
(
(IsNull({EMEM.Location}) or Trim({EMEM.Location}) = "")
AND
NOT IsNull({Job})
) [COLOR=green]// Has job Only[/color]

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

 

[jmd0252]

if you look in the formula editor,, you will see how all your selections are written,, make the very last part of the selection refernce the field you are trying to check for "blank"..

 

[wanzek]

Turkbear-

Your formula only gives me the jobs that I don't want to print.

{EMEM.Status}='A' and

(
(IsNull({EMEM.Location}) or Trim({EMEM.Location}) = "")
AND
IsNull({EMEM.Job})
) // Has No Job and No Location

OR

(
IsNull({EMEM.Job})
AND
{EMEM.Location} In ['SOLD','UNK','X-KM','JUNK']
) // Has Locations wanted and NO Job

OR
(
(IsNull({EMEM.Location}) or Trim({EMEM.Location}) = "")
AND
NOT IsNull({EMEM.Job})
) // Has job Only

 

[Turkbear]

Hi,
OOps,
The last criteria is incorrect, leave it off

(
(
(IsNull({EMEM.Location}) or Trim({EMEM.Location}) = "")
AND
IsNull({EMEM.Job})
) [COLOR=green]// Has No Job and No Location[/color]

OR

(
( IsNull({EMEM.Job}) OR Trim({EMEM.Job} = "')
AND
{EMEM.Location} In ['SOLD','UNK','X-KM','JUNK']
) [COLOR=green]// Has Locations wanted and NO Job [/color]

)

AND

{EMEM.Status}='A' [COLOR=green]applies to all records][/color]


[profile]
[COLOR=blue]
To Paraphrase:"The Help you get is proportional to the Help you give.."
[/color]