In a query can I write...

  • Thread starter Thread starter ryan.fitzpatrick3
  • Start date Start date
R

ryan.fitzpatrick3

In a query can I write in the criteria.


Not Like "NONE" And Is Not Null?

None is actually what shows up in the table i.e. item numbers

123456
321546
NONE
112654
NONE
NONE

this is how it shows up.

Ryan
 
My we are in rare form today, Crystal.
But, you never know....
I needed a good laugh today, thanks.
 
Hi Dave,

thanks ... I think <grin>

nice to see you! So how about that watch?

Warm Regards,
Crystal

*
(: have an awesome day :)
*
 
If you are asking how you would code this with VBA, the answer is:
YourField <> "NONE" And Not IsNull(YourField)

"Is not null" is SQL syntax. In VBA, you use the IsNull() function.
"Like" is also SQL syntax. Logically, it is incorrect to use the Like
operator when you are not using wildcard characters and possibly
inefficient. Use "=" instead of Like whenever you always have the entire
value you are searching for. Using "Like" in a query may cause the query
engine to ignore an otherwise useful index and use a full table scan
instead. This could result in unnecessary slowness.

A final caveat, when working with text values, you may need to account for
zero-length strings in addition to nulls so I would actually code the above
statement as:
YourField <> "NONE" And YourField & "" <> "" <--- this will handle both
nulls and ZLSs in a text field. Numeric fields CANNOT contain ZLSs.

In SQL, I would use exactly the same expression:
YourField <> "NONE" And YourField & "" <> ""
 
I appreciate the help, I was doing this in SQL syntax, I don't do VBA
because I don't know how! lol. Basically in the criteria of the query
in the design view I want no nulls and to get rid of the "NONE"
recordsets. Also if I'm looking for item numbers , how do I not look
for some.

i.e.
000011
000012
000013

I dont want these to show up, is it not like "000011" and not like
"000012' and not like "000013"
Can you help with that. Non VBA would be nice! Thanks.

Ryan
 
Back
Top