Is It Really Null?

[PC User]

In using a filter criteria "Is Null" or "Is Not Null" I read in the help for A2K that if the text or memo field has a space typed into it and no other text, that the filter critera states that the field "Is Not Null". I'm looking for a way to check for an empty text or memo field that has no characters typed into it even though the user may have accidently typed a space into it. That means that a text or memo field may look empty, but may still contain a space. If so the "Is Null" criteria doesn't help me.

In other words, I'm trying to build a critera into the filter that selects text or memo fields that are empty of text even though the user may have typed a
space into it.

Thanks,
PC

 

[KenHigg]

One quick way to do this is to combine the null test and the "" test with an "or".

Is null or Like ""

Would this work?

 

[PC User]

It Really Null?

I get a syntax error now. I changed using two of the double quotes in [Comments] Is Null Or Like "" to two of the single quotes [Comments] Is Null Or Like '' and I still get a syntax error. I tried [Comments] Like "" and the results are the same as [Comments] Is Null.

Thanks,
PC

 

[Happy YN]

try using brackets
((isnull) or (like ""))

 

[RV]

Use the Len function.

RV

 

[KenHigg]

Here's the sql where I just got it to work:

SELECT tblShortages.CREATEDBY
FROM tblShortages
WHERE (((tblShortages.CREATEDBY) Is Null Or (tblShortages.CREATEDBY) Like ""));

However, I'm not sure what will happen if you're testing a memo fld...

 

[PC User]

Thank you guys. That seems to be the answer. I used [Comments] Is Null Or [Comments] Like "" and got it to filter correctly. If its ok, I have another question on another part of my filter building. I'm filtering by [status] and have a problem. For status there are four possibilities.

Complete
Deliquent
In Progress
Pending

I have a filter that filters by any of the four possibilities; however, I now need a filter that tell me that a workorder (WO) is either complete or not complete. So I tried criterial [Status] = "Complete" to create a report to show all completed WO and I tried [Status] <> "Complete" to create a report to show all WO that are not complete (meaning that the WO has any other status, but being complete). I'm not getting the desired results with the criteria [Status] <> "Complete". Does anyone see the error that I'm making?

Thanks,
PC

 

[KenHigg]

Have you tried a 'Not Like' instead of the '<>'?

 

[Mile-O]

How about Nz([FieldName], "") ?

 

[Bat17]

even though the user may have typed a space into it

Don't think he can

Peter

 

[PC User]

Thanks guys for your responses. I'm making progress. My filter combined several comboboxes and I use a Debug.Print to show how they're doing.

Test 1:
WHERE [ResponsibleParty] = "John Doe" And [Status] Not Like "Complete"
Test 2:
WHERE [ResponsibleParty] = "John Doe" And [Status] Not Like "Complete" Or [Status] Is Null
Test 3:
WHERE [ResponsibleParty] = "John Doe" And [Status] Not Like "Complete" Or [Status] Is Null Or [Status] Like ""

When I use Test 1 I get all the other [Status] possibilities I mentioned. But sometimes the user forgets to enter a status and the [Status] field is empty so I tried Test 2 to try to include all other [Status] possibilities including empty fields and Test 2 give results that ignore the [ResposibileParty] field and show me everyone in the database, not just "John Doe". But I do get all other possibilities of [Status] including empty fields. I tried Test 3 and the results are the same as Test 2. I don't understand why Test 2 ignores my selected [ResponsibileParty] and shows results for all Responsible Parties in the database. Does anyone see another way?

Thanks,
PC

 

[izyrider]

for many values:
X and Y or B
<> (X and Y) or B
<> X and (Y or B)

try a few parentheses.



meanwhile, (and you should play with parentheses anyway for the exercise):
[Status] Not Like "Complete" Or [Status] Is Null
could be replaced with
nz([Status], "") not like "Complete"


izy

 

[PC User]

That did it!! Thank you izyrider. And thank you to the other guys who contributed their help. The solution is: Nz([Status], "") Not Like "Complete"

PC

 

[Mile-O]

Why are you using the Like operator without a wildcard. Just use <> rather than Not Like as that's the correct operator.