Solved

Access query using the "Like" command

Posted on 2007-03-19
23
7,015 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 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

25 Experts available now in Live!

Get 1:1 Help Now