Correct Expression Help

  • Thread starter Thread starter JCrowe
  • Start date Start date
J

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
 
JCrowe said:
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
Click to expand...

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
 
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 ?
Click to expand...

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!
 
Back
Top