Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

Access 2007 - Query syntax

My OS is windows 7 and I use Access 2007.  The table has a marker field which is a text type, and some of the records are marked with X.  I would like to retrieve all the records that do not have an X.  What is the syntax please.  I tried <>X; !=X; NOT X, but they all do not work.  Looks like I am having the wrong syntax.  Thank u.
0
jegajothy
Asked:
jegajothy
  • 12
  • 9
  • 3
  • +1
3 Solutions
 
CEHJCommented:
How about
select * from foo where bar <> 'X'

Open in new window

0
 
for_yanCommented:


http://office.microsoft.com/en-us/access-help/examples-of-query-criteria-HA010066611.aspx

it says Not Like "*X*"

or Not "X"

if you cannot have "X1" or something

0
 
for_yanCommented:
It looks like in your experiments you were not using quotes - but they are necessary - in the examples in the link above they are double quotes;
perhaps single quotes may work either
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jegajothyretiredAuthor Commented:
In response to CEHJ, the above does not work.
I have  this in the syntax :
SELECT [tblOct-Nov-Dec 2011].*
FROM [tblOct-Nov-Dec 2011]
WHERE Marker <> 'X';

Marker is the name of the Text type field.  Thank u for your response.
0
 
for_yanCommented:
Try double quotes - maybe it will work ?
0
 
jegajothyretiredAuthor Commented:
I tried this too and it does not work :
SELECT *
FROM [tblOct-Nov-Dec 2011]
WHERE ((([tblOct-Nov-Dec 2011].Marker)<>'X'));
0
 
CEHJCommented:
Try
SELECT * FROM "tblOct-Nov-Dec 2011" WHERE "tblOct-Nov-Dec 2011.Marker" <>'X'

or

SELECT * FROM "tblOct-Nov-Dec 2011" WHERE "tblOct-Nov-Dec 2011".Marker <>'X'

Open in new window

0
 
jegajothyretiredAuthor Commented:
In response to everyone, I have tried all one by one, and none seem to work.  thanks.
0
 
for_yanCommented:
Maybe the bes way is to create a simple query in Access itseklf and then exxport its SQL
Though I'm not sure it is easy to do tyhe last step in Access - it is some obscure option in the menu I guess
0
 
for_yanCommented:


That is how I exported from MS Access (as it created itself) and the query works:
("Mailing List" is  table name)


I have MSAccess 2003 but it is the same thing I hope
SELECT [Mailing List].FirstName
FROM [Mailing List]
WHERE ((([Mailing List].FirstName)<>"X"));

Open in new window

0
 
jegajothyretiredAuthor Commented:
in response to for yan, here is my syntax, hope u can review if I made any typos or not, but it did not product any results :
SELECT [tblOct-Nov-Dec 2011].MARKER
FROM [tblOct-Nov-Dec 2011]
WHERE ((([tblOct-Nov-Dec 2011].MARKER)<>"X"));
0
 
CEHJCommented:
The other table name contains hyphens and spaces though
0
 
for_yanCommented:
Yes seems exact but I exported it from acces and it worked - maybe you don't have X
0
 
slycoderCommented:
Try:

SELECT [tblOct-Nov-Dec 2011].*
FROM [tblOct-Nov-Dec 2011]
WHERE ((([tblOct-Nov-Dec 2011].MARKER) Not Like "*X*"));

0
 
for_yanCommented:
Can you try to make a test table with more conventional name and try the query I posted?
0
 
jegajothyretiredAuthor Commented:
In response to for yan, I changed  the name of the table but left the field Marker as it is.
and I tried these :
These do not work :
SELECT tblWithoutX.mARKER
FROM tblWithoutX
WHERE (((tblWithoutX.mARKER)<>"X"));

SELECT tblWithoutX.mARKER
FROM tblWithoutX
WHERE (((tblWithoutX.mARKER) Not Like "X"));
0
 
for_yanCommented:
Can yoi open MDB with access and try to run this query manually?
0
 
for_yanCommented:
I mean through normal Access interface?
0
 
jegajothyretiredAuthor Commented:
In response to for yan, I open Access and then highlight the MDB file and open it.
Thence I run the query, after modifying it.
If that is what u mean.
0
 
for_yanCommented:
So you run it without SQL - just like normal MS Access users do, through visual inteface?
And does it return stuff which you expect?
0
 
jegajothyretiredAuthor Commented:
in rersponse to for yan, the answer is No.  I do not get any results.
0
 
for_yanCommented:
Then probably there are no rows in your table which satisfy this conditiion ?
0
 
jegajothyretiredAuthor Commented:
in response to for yan, there are row with X, because when I run with field=X, I get results.
0
 
for_yanCommented:
But are there rows which do not staisfy field = 'X' annd if there are such why you are bnot finding them even with
GUI based Access?
0
 
jegajothyretiredAuthor Commented:
thank u everyone.
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.

  • 12
  • 9
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now