Solved

Access query using the "Like" command

Posted on 2007-03-19
23
7,017 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

808 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