Searching for blank

[DonH]

This should be and probably is a no-brainer, but when I want to find fields that have nothing in them, or find ones that don't have nothing in them, what do I type in?

I've tried <>"" <>'' "" AND ""

I can't find how FileMaker handles NULL

[-Queue-]

I answered this last week in another thread you started. Did you miss it?

[Lee Smith]

Hint, what do null and = have in common?

[DonH]

Oops. Yes I did miss it. I was focused on the date problem.

Null and = have nothing in common for most normal people.

FM needs to correct this so it is very clear. Like = NULL or = "" Not just = That way we can also have <> NULL or <> ""

So much better

[Lee Smith]

Actually I was referring to the answer as it related in thread, not how FileMaker would have answered what = means

In FileMaker terms, The = sign equals exact match. So if you do a find with the = entered into a field by itself, it is telling FileMaker to find all records that equal nothing in that field.

HTH

[-Queue-]

Well, in their defense, while it isn't common, = is somewhat logical to me. Equals what? Equals nothing, zip, nada, NULL! Oh, and ="" will work fine on text fields; it doesn't make much sense on non-text fields anyway as "" signifies literal text in FileMaker. And the Omit option takes the place of <>.

I much prefer = to ===NULL, which took me ages to figure out in PHP.

[DonH]

Hmmm. ="" doesn't work on text fields. I tried it.

PHP? You should go with Cold Fusion. CF doesn't like NULL either.

You see, us old old programmers realize that for true data integrity you have to have SOMETHING there. Not NOTHING as in = This is too ambiguous.

Now my problem is I'm trying to find the data in a date range BUT there must be something in another field too. So if I do the date range, it works. If I do the = [omit] it works (sometimes). If I do BOTH it doesn't work.

I realize that the OMIT appears to be a NOT so I would then need to say to search for everything outside of my date range and with something in that field. Then use OMIT. Logically it is now NOT outside the date range AND NOT blank.

It returned all records.

[-Queue-]

Well, it works for me, at least.

In PHP, NULL isn't always the same as "", but I do always set possible blank data to NULL before testing it and I use ===, otherwise it sees "" and 0 as possibly NULL. In FileMaker, however NULL and "" would be similar. There is no such thing as a NULL field unless it doesn't exist in the database. So IsEmpty(), not Length(), and = can all test for 'NULL' as it pertains to FileMaker.

Putting ="" in, say, a date field in FileMaker is illogical according to the fields date type, as "" is text and must be converted when scripted, e.g. Set Field[datefield, TextToDate( "" )] works whereas Set Field[datefield, ""] produces a question mark in the field, signifying invalid data.

Try using more than one request. Make your first request the date range and the second an omitted '=' in the other field. This would be equivalent to an AND NOT.

[DonH]

Maybe the problem is it is a repeating field? Thus if ANY entry in the field has something it will pull them all up even the ones without anything?

If so, is there anyway just to get the records that have entries?

[Lee Smith]

Don,

I said that already. Of course it was in response to your other post.

This one is starting to sound an awful lot like that one now.

;(