mancoi
asked on
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.+
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.+
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.)
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]
(purchaseorderdetails is the name of the database where notes comes from. I like using "Su" just in case.)
You have too many opening parentheses. Try:
NZ([purchaseorderdetails]. [notes],"z z") Not Like "Su*"
NZ([purchaseorderdetails].
ASKER
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
It prompts you for what ?
Check you screen shot. Your Like clause is certainly not what I gave you.
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
Test this:
>"Superseded" or IsNull or ="" >> two sets of double quotes.
I just emulated this and get the expected results.
mx
Sorry ... s/b
<>"Superseded" or IsNull or ="" >> two sets of double quotes.
<>"Superseded" or IsNull or ="" >> two sets of double quotes.
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
The field property should by NZ([purchaseorderdetail].[
The criteria property should by Not Like "su*"
and you should repeat it on the second criteria line also
ASKER
<>"Superseded" Or "IsNull" Or =""
this does not work......
I want to display every record besides the notes field that contains the work "Superseded".
Thanks
this does not work......
I want to display every record besides the notes field that contains the work "Superseded".
Thanks
When in desing view of your query, click on menu View->SQLView and copy the SQL statement and paste it in this thread...
ASKER
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
Let me know what you think,
Does anyones else need more screen shots?
http://download.yousendit.com/155EA3FB0848D920
Like I said :
The FIELD property should by NZ([purchaseorderdetail].[ Notes], "")
The CRITERIA property should by Not Like "su*"
The FIELD property should by NZ([purchaseorderdetail].[
The CRITERIA property should by Not Like "su*"
ASKER
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
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
The FIELD property should be NZ([purchaseorderdetail].[ Notes], "")
The CRITERIA property should be Not Like "su*" on both lines
The CRITERIA property should be Not Like "su*" on both lines
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
I created a table with that sort of data ... and I'm looking at it right now.
??
mx
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 :)
>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 :)
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.
ASKER
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
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
Thanks, glad to help.
Glad I could help !
Good luck with your project
Good luck with your project
<>"Superseded" or IsNull
m