In a query can I write...

[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

 

[mscertified]

Try:

MyColumn <> 'NONE' And MyColumn Is Not Null?

-Dorian

 

[strive4peace]

just adding on ... without the question mark <smile>

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[Klatuu]

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

 

[strive4peace]

Hi Dave,

thanks ... I think <grin>

nice to see you! So how about that watch?

Warm Regards,
Crystal

*
have an awesome day
*

 

[Klatuu]

I definite thanks.
Never heard any more about the watch. Don't know what he did with it.

 

[PatHartman]

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 & "" <> ""

 

[ryan.fitzpatrick3]

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

 

[strive4peace]

Hi Pat!

Nice to see you <smile>

Warm Regards,
Crystal

*
have an awesome day
*

 

[strive4peace]

Hi Ryan,

you can put this in your criteria:

criteria --> Not Like "00001?"

you could also do this:

<> "000011" AND <> <> "000012" AND <> "000013"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*