Access query using the "Like" command

Hello,

I am having trouble with my Access query. I have a field that I would like to filter out by certain letters in that field.
This field is called Notes.
Each query will output about 5 - 10 rows.
In the Notes field it will be either blank, Superseded, ETS or cancelled. ( there will be numbers or words following those words I just listed except for the blank field of course)
I do not want to output any rows that have the word Superseded in that particularfeild called Notes.

I use the criteria of     Not Like "Su*"    for the field called Notes.
This work for the fields that have the word Superseded in it BUT it will also work for rows that have a blank Notes field. I want to show the rows that have the blanks field in Notes.
I tried everything I could think of and nothing will work for me. like "*S" or even typing out the full word like "Superseded*". I even tried to put a value before the S into the database like @Superseded and filter "@*" and it did not work.
Please let me know if you need more clarification,
Thank-you for your time to take a look.+
mancoiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jmantha709Commented:
Probably because your field are NULL ?  Try this criteria :
NZ(Notes, "") Not Like "Superseded*"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Put this in the criteria cell:

<>"Superseded" or IsNull

m
0
GRayLCommented:
Try this.

Nz(Notes,"zz") Not Like "Superseded*"
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

mancoiAuthor Commented:
Those fields are NULL ( blanks).
I tried your solution and it prompts me.
I cannot use prompting. This is used to integrate with another application.
I will be used for its query alone.

I used   (NZ([purchaseorderdetails].[notes],"") Not Like "Su*"

(purchaseorderdetails is the name of the database  where notes comes from. I like using "Su" just in case.)
0
GRayLCommented:
You have too many opening parentheses.  Try:

NZ([purchaseorderdetails].[notes],"zz") Not Like "Su*"
0
mancoiAuthor Commented:

None worked.....<>"Superseded" or IsNull
or......Nz(Notes,"zz") Not Like "Superseded*"

Here is a screen shot of my Access.

http://download.yousendit.com/F40949E943065B85
0
jmantha709Commented:
It prompts you for what ?
0
GRayLCommented:
Check you screen shot.  Your Like clause is certainly not what I gave you.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Are you sure the fields are Null or Empty Strings ... big difference
Test this:

>"Superseded" or IsNull or =""   >> two sets of double quotes.

I just emulated this and get the expected results.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sorry ... s/b

<>"Superseded" or IsNull or =""   >> two sets of double quotes.
0
jmantha709Commented:
Looking at your screenshot :

The field property should by NZ([purchaseorderdetail].[Notes], "")
The criteria property should by Not Like "su*"
and you should repeat it on the second criteria line also
0
mancoiAuthor Commented:
<>"Superseded" Or "IsNull" Or =""

this does not work......
I want to display every record besides the notes field that contains the work "Superseded".


Thanks
0
jmantha709Commented:
When in desing view of your query, click on menu View->SQLView and copy the SQL statement and paste it in this thread...
0
mancoiAuthor Commented:
jmantha709: I got a screen shot of BOTH screens into one work doc.
Let me know what you think,

Does anyones else need more screen shots?

http://download.yousendit.com/155EA3FB0848D920
0
jmantha709Commented:
Like I said :

The FIELD property should by NZ([purchaseorderdetail].[Notes], "")
The CRITERIA property should by Not Like "su*"
0
mancoiAuthor Commented:
It is not prompting me anymore after I took out the tablename.field

I still does not work.

Here is a screen shot of a normal output without the        Not Like "Su*"    command.

http://download.yousendit.com/F18D9E7E5CCD5D81
0
jmantha709Commented:
The FIELD property should be NZ([purchaseorderdetail].[Notes], "")
The CRITERIA property should be Not Like "su*" on both lines
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
It should return ALL records including those with Null ... excluding "Superseded"
I created a table with that sort of data ... and I'm looking at it right now.

??

mx
0
jmantha709Commented:
Try to post the SQL Statement :

>When in desing view of your query, click on menu View->SQLView and copy the SQL statement and >paste it in this thread...

This will help us help you...

Sorry but leaving for the day, I may check back later...  Meanwhile, I'm sure the other experts will be glad to follow up :)
0
GRayLCommented:
In the query designer, right click  the big blue bar at the top and select SQL View.  This will give you the query string that we want you to paste into a comment window in this question so we all can see what you've made.
0
mancoiAuthor Commented:
WE GOT IT !!!!

I have been working on this for days and days.....I made 3 different posts. Finally I can start my next portion of my project. ( using a bulk mailer to send emails to customers by pulling their data from this access query)

jmantha709: I did exactly what you said and it worked....I needed it in the field not in criteria.
GRayL: You had the right idea but I was putting it in the wrong spot.


Thanks again
0
GRayLCommented:
Thanks, glad to help.
0
jmantha709Commented:
Glad I could help !

Good luck with your project
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.