Text criteria in Query (1 Viewer)

Andy Ralph-Kineteco

New member
Local time
Today, 14:15
Joined
Mar 12, 2014
Messages
6
Hi all, Having a senior moment I presume...
I am using Access 2013 under Windows 7.
I have a table (tblStock) with a field called "Description".
Some 25% of the records hold the following data - "DNU - ....." (meaning "Do Not Use - ....) the others hold data such as "Spring" or "Plunger".
In a Select Query to find all records without this prefix in the Description Field, I tried to use the following criterion:
Not "dnu*"
This returned no records at all.
I tried:
Not Like "dnu*"
again no records and it also changed the criterion to:
Not Alike "dnu*"
Any ideas please? :banghead:
Thanks
Andy Ralph
 

pr2-eugin

Super Moderator
Local time
Today, 14:15
Joined
Nov 30, 2011
Messages
8,494
Try this code,
Code:
SELECT tblStock.theFieldA, tblStock.theFieldB FROM tblStock
WHERE Left(tblStock.Description, 3) <> "DNU";
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:15
Joined
Feb 19, 2013
Messages
16,553
You havent stated your full criteria - I presume you are using something like

WHERE DescriptionFld Not Like "dnu*"

Is your db SQL syntax is set up as ANSI92? (go to Form>Options>Object Designers and look under query design to check)

if it is then your code should be

WHERE DescriptionFld Not Alike "dnu%"

I've also recently been experiencing issues with case sensitivity i.e. 'DNU'<>'dnu'. Not sure why, haven't had time to investigate

so also try

WHERE DescriptionFld Not Alike "DNU%"

or

WHERE DescriptionFld Not Like "DNU*"

depending on the SQL syntax you are using

An alternative though less efficient criteria to try is

WHERE InStr(DescriptionFld,"DNU")<>1
 

Andy Ralph-Kineteco

New member
Local time
Today, 14:15
Joined
Mar 12, 2014
Messages
6
Perfect! It was the percentage sign % rather than the asterisk * that I needed to use!
I did not understand the significance of ANSI92 - I'd better go look it up now!
:)
 

Users who are viewing this thread

Top Bottom