Solved

Access query using the "Like" command

Posted on 2007-03-19
23
7,028 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

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
 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

617 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