Compare part of one field with another field

[geronimo_me]

Hi,

I'm trying to compare two TITLE fields from different tables to locate
anomolies - however Table1 TITLE field contains a dot(.) after some of
the entries, however i want to remove the dot so i can compare properly
(the dot is irrelavant to my comparisons):

Record1
Table1 TITLE = MISS.
Table2 TITLE = MISS
Record2
Table1 TITLE = MISS
Table2 TITLE = MISS

The two example above are NOT anomolies even though Record1/Table1
includes a (dot) - is ther any way in the QBE window that i can compare
part of one field (without the dot) with another field?

Please help

Flick

[AP]

You could use the Instr() function to determine the location of the "."
then pull the leftmost characters before the PERIOD,

=iif(InStr(1,[TITLE],".")=0,[TITLE],left([TITLE],InStr(1,[TITLE],"."))

[Johnny Meredith]

You could branch to a function that loops through each character in the
string, rejecting the irrelevant characters (air code):

Sub Foo()
..
..
strGoodString = RemoveIrrelevan tCharacters(str BadString)
..
..
End Sub

Function RemoveIrrelevan tCharacters(val ue As String) As String
Dim strTemp as String
Dim i as Integer
For i = 1 to Len(value)
If MID(value,i,1) <> "." Then
strTemp = strTemp & Mid(value,i,1)
End If
Next
RemoveIrrelevan tCharacters = strTemp
End Function

Johnny

[geronimo_me]

Would I place this in the qbe window? I'm a little unfamiliar with
Access

Many Thanks

Flick. x

[AP]

Johnny' s response would require knowledge of VBA, while his method
would also work well, it is a bit more complicated. If you choose to
use the expression mentioned in my previous post, you would enter it in
the query window as an expression.

[geronimo_me]

Thankyou very much for your help - i've place the expression you
suggested into the QBE window and then run the query - all this
expression seems to do is duplicate what Table1 Field1 displays. ie:

Record1
Table1 TITLE = MR.
Table2 TITLE = MR
Exp = MR. (same as Table1 TITLE)
Record2
Table1 TITLE = DR.
Table2 TITLE = DR
Exp = DR. (same as Table1 TITLE)

Any idea?

Thank you once again

Flick. x

[rkc]

ge*********@hot mail.com wrote:

Hi,

I'm trying to compare two TITLE fields from different tables to locate
anomolies - however Table1 TITLE field contains a dot(.) after some of
the entries, however i want to remove the dot so i can compare properly
(the dot is irrelavant to my comparisons):

Record1
Table1 TITLE = MISS.
Table2 TITLE = MISS
Record2
Table1 TITLE = MISS
Table2 TITLE = MISS

So an anomaly in this case is a value in table1 that isn't in
table2?

Maybe...

SELECT Table1.Title
FROM Table1
LEFT JOIN Table2
ON Table1.Title = iif(Right(Table 2.Title,1) =".",
Left(Table2.Tit le, Len(Table2.Titl e)-1),
Table2.Title)
WHERE (((Table2.Title ) Is Null));