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: 1044
  • Last Modified:

How do you force an uppercase search in a query?

i cannot seem to filter my query with uppercase characters
...eg I cannot get it to filter values which contain "DR" and not "dr"

can anyone help?
0
mlnfis
Asked:
mlnfis
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
MrXmasCommented:
mlnfis,

Set your where clause to use the StrComp function.

So, if you want to find where MyFieldName = "DR" (and not "dr") then use this:

SELECT * FROM MyTableName WHERE StrComp(MyFieldName,"DR",vbBinaryCompare)=0

The StrComp function with the vbBinaryCompare will return a 0 only if the two strings are identical.

Hope that helps,

--Jim Christmas
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I think in the query instead of vbBinaryCompare you will need to use 0  (zero) ... the literal value for vbBinaryCompare

mx
0
 
Rey Obrero (Capricorn1)Commented:
you can use the strComp in a query like this

select FieldName
from TableX
where StrComp([Fieldname],"DR",0)=0

vbBinaryCompare is not recognized in a query
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
MrXmasCommented:
DatabaseMX and Capricorn1,

Good catch guys - Thanks.

--Jim Christmas
0
 
mlnfisAuthor Commented:
Hi Guys

Many Thanks for those answers..and, sorry, my SQL is pretty basic so slowly  I'm creeping my way to the answer!

With a table called [UIDC] and a fieldname [Company] the following:

SELECT UIDC.Company
FROM UIDC
WHERE StrComp([Company],"DR",0)=0;

Does indeed bring up any record with 'DR' as the whole field, but only where it is the whole field.

Can anyone help with if I need to bring up e.g. 'DR Jones' - ie where 'DR' can be followed by anything - I assumed it might work with the '*' (asterix) symbol - but can't seem to get this to work no matter how many or where I place them? ie

WHERE StrComp([Company],"DR*",0)=0;

Many Thanks
Mark
0
 
Rey Obrero (Capricorn1)Commented:
try this

SELECT UIDC.Company
FROM UIDC
WHERE StrComp(Mid(Trim([Company]),1,2),"DR",0)=0;
0
 
mlnfisAuthor Commented:
Hi people

Many many thanks to all of you - that last solution worked great - has made my day at work much easier!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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