Only if upper case

[SqlDope]

Hello, I wonder If any one can help me.
Windows XP Access 2003
I keep databases of headstone inscriptions.
My table has the fields: ID, Surnames, Date of birth, Date of death,
Inscription ans a few others.
The Inscription field contains the whole inscription as read from the
headstone with "\" denoting a new line.
Two (simplified) examples:
(1) John\Son Of\Mary And Henry ORD\Died 24th June 1786\Aged 42 Years
(2) Harry SMITH\For 21 Years Secretary Of The Order Of Buffulows Died ......
Note that (1) has the Surname ORD and (2) contains the word Order.
I wish to select all records that have the surname ORD but none with the
word Order (unless they also contain Surname ORD).
Here is my first query;
SELECT Inscription FROM Cemetery WHERE Inscription LIKE "*ORD*";
But this also selects inscription (2)
Second Attempt
SELECT Inscription FROM Cemetery WHERE Inscription LIKE (StrComp 0,"*ORD*")
But of course it doesn`t work
Any help appreciated
Thanks And Regards

 

[Guest]

Enter a calculated field in a new column in your query with this formula. If
you want to name it add the name as follows: CustomName: formula

InStr(1, [Inscription], "ORD", 0)

In the criteria in this column (field) enter say - Not 0 (zero). This is
because if the string being sought is not found within the string being
search, the formula returns 0.

I have included the contents of the help file for this function for your
reading pleasure. The trick to this function is the specification 0 (zero)
as the last parameter in the function. If you read below, zero corresponds
with a Binary Comparison and not a Text Comparison. You want Binary because
it distinguishes between upper case and lower case of the same letter. Also,
you must enter the case you are looking for where I placed ORD. If you enter
all lower case letters, you would only see records containing those lower
case letters.

Happy querying!

Seth
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

InStr Function


Returns a Variant (Long) specifying the position of the first occurrence of
one string within another.

Syntax

InStr([start, ]string1, string2[, compare])

The InStr function syntax has these arguments:

Part Description
start Optional. Numeric expression that sets the starting position for each
search. If omitted, search begins at the first character position. If start
contains Null, an error occurs. The start argument is required if compare is
specified.
string1 Required. String expression being searched.
string2 Required. String expression sought.
compare Optional. Specifies the type of string comparison. If compare is
Null, an error occurs. If compare is omitted, the Option Compare setting
determines the type of comparison. Specify a valid LCID (LocaleID) to use
locale-specific rules in the comparison.



Settings

The compare argument settings are:

Constant Value Description
vbUseCompareOption -1 Performs a comparison using the setting of the Option
Compare statement.
vbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs a textual comparison.
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on
information in your database.



Return Values

If InStr returns
string1 is zero-length 0
string1 is Null Null
string2 is zero-length start
string2 is Null Null
string2 is not found 0
string2 is found within string1 Position at which match is found
start > string2 0

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

 

[Arvin Meyer [MVP]]

Access (and Windows itself) is not really case sensitive. You can use the
built-in Find mechanism to search for each instance of your search string.
You can also search using the ANSI character strings in a query:

Select * From MyTable Where MyField Like "*" & Chr(79) & Chr(82) & Chr(68) &
"*"

which will find all records which have anything, ORD, and anything.

You'll need an ANSI Character table to look them up. Here's one:

http://en.wikipedia.org/wiki/ASCII

you are looking for the Decimal character codes between 32 and 127 (95
printable characters)

 

[tina]

I wish to select all records that have the surname ORD but none with the

word Order (unless they also contain Surname ORD).

if you want to select records with a specific surname, why are you setting
criteria on the Inscription field instead of the Surnames field?

hth

 

[SqlDope]

Hello Seth, Arvin and Tina
Thanks for taking the trouble and time to reply.
I am using sql and vb for this query because I have lots of searches to do.
I didn't know that the Instr function was available in ms sql
Using Chr$(x) is not good because I need to automate this using a list
I need to pull out all inscriptions with ORD then SMITH The BROWN etc
Good point Tina I wonder Why?
Besr Regards To All