Correct Expression Help

[JCrowe]

I have a field that contains a possiblity of three
different values:

1. Field is empty (no data entered)
2. The word "FREE"
3. An invoice number

What I would like to do is build my query so that the
empty records and FREE records are not pulled up. Just the
records with an invoice number.

I have played with a couple of different expressions and
found that:

<> Free

Excludes both empty records and Free record. My question
is why ? And is this a safe way to go or is there
something better ?

Thanks,
JCrowe

 

[Dirk Goldgar]

I have a field that contains a possiblity of three
different values:

1. Field is empty (no data entered)
2. The word "FREE"
3. An invoice number

What I would like to do is build my query so that the
empty records and FREE records are not pulled up. Just the
records with an invoice number.

I have played with a couple of different expressions and
found that:

<> Free

Excludes both empty records and Free record. My question
is why ? And is this a safe way to go or is there
something better ?

Thanks,
JCrowe

From the fact that the field can contain the word "FREE", I conclude
that the field in question is a text field. As such, something like
this might do the job on the criteria line in the query design grid:

<> "FREE" And Is Not Null

 

[John Vinson]

I have played with a couple of different expressions and
found that:

<> Free

Excludes both empty records and Free record. My question
is why ? And is this a safe way to go or is there
something better ?

Just for clarity and self-documentation I'd be inclined to use a
criterion of

<> "Free" OR IS NULL

The null records will fail any comparison: NULL is not equal to
anything, nor is it UNequal to anything, so your criterion will indeed
work correctly. My suggestion will work too and may be a bit easier to
understand when you come back to this query later!