Solved

Access query using the "Like" command

Posted on 2007-03-19
23
7,010 Views
Last Modified: 2012-05-05
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.+
0
Comment
Question by:mancoi
  • 8
  • 6
  • 5
  • +1
23 Comments
 
LVL 15

Accepted Solution

by:
jmantha709 earned 350 total points
ID: 18751100
Probably because your field are NULL ?  Try this criteria :
NZ(Notes, "") Not Like "Superseded*"
0
 
LVL 75
ID: 18751117
Put this in the criteria cell:

<>"Superseded" or IsNull

m
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 150 total points
ID: 18751144
Try this.

Nz(Notes,"zz") Not Like "Superseded*"
0
 

Author Comment

by:mancoi
ID: 18751154
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
 
LVL 44

Expert Comment

by:GRayL
ID: 18751178
You have too many opening parentheses.  Try:

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

Author Comment

by:mancoi
ID: 18751201

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
 
LVL 15

Expert Comment

by:jmantha709
ID: 18751203
It prompts you for what ?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 18751264
Check you screen shot.  Your Like clause is certainly not what I gave you.
0
 
LVL 75
ID: 18751270
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
 
LVL 75
ID: 18751287
Sorry ... s/b

<>"Superseded" or IsNull or =""   >> two sets of double quotes.
0
 
LVL 15

Expert Comment

by:jmantha709
ID: 18751310
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:mancoi
ID: 18751337
<>"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
 
LVL 15

Expert Comment

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

Author Comment

by:mancoi
ID: 18751367
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
 
LVL 15

Expert Comment

by:jmantha709
ID: 18751385
Like I said :

The FIELD property should by NZ([purchaseorderdetail].[Notes], "")
The CRITERIA property should by Not Like "su*"
0
 

Author Comment

by:mancoi
ID: 18751386
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
 
LVL 15

Expert Comment

by:jmantha709
ID: 18751410
The FIELD property should be NZ([purchaseorderdetail].[Notes], "")
The CRITERIA property should be Not Like "su*" on both lines
0
 
LVL 75
ID: 18751424
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
 
LVL 15

Expert Comment

by:jmantha709
ID: 18751441
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
 
LVL 44

Expert Comment

by:GRayL
ID: 18751490
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
 

Author Comment

by:mancoi
ID: 18751626
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
 
LVL 44

Expert Comment

by:GRayL
ID: 18751942
Thanks, glad to help.
0
 
LVL 15

Expert Comment

by:jmantha709
ID: 18753605
Glad I could help !

Good luck with your project
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now