Finding a text field in a field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi this is a repost actually.

I'm not sure if this can be done in a query by creating a function first.
Here's an example: I need to find NICKJM in the "HIER" field. If there is
none, then it would look for NICKJ, and if there is no value it would look
for NICK. It will do this until it has matched. Now the value NICKJM is an
example and it can be anything; MMHGA, LKEDK. Once the value is found in
Hier it will place the need to find field in the new field. Is this poosible
to do in a
query?

NICKJM
 
PARAMETERS [Enter Name] Text ( 255 );
SELECT YourTable.*
FROM YourTable
WHERE YourTable.HIER=[Enter Name]
OR YourTable.HIER)=Left([Enter Name],5
OR YourTable.HIER)=Left([Enter Name],4 ;
 
Hi Jerry

Thank you for your response. This doesn't appear to be working. [Enter
Name] is a field from another table. Also, once value is found in the Hier
field, it will no longer be necessary to search the remaining left[Enter
Name]#. So if say NICKJ is the first value in the [Enter Name] field (Enter
name is going to have a lot of different values). In the Hier field there is
no NICKJ but a NICK, so in the new created field the NICKJ will be placed.
Even though there might be a value NIC, in the hier field, it won't place
NICKJ in the new field there because it has already found NICK. This is why
I'm thinking a function should be created similar to the one listed below.
This isn't work quite like I want though. Any modifcation to the function or
a new function would be appreciated.

Function FindText(StrIn As String, LookFor As String) As String
Dim intOK As Integer
Dim strFound As String
strFound = LookFor
Dim intX As Integer
intX = 1

intOK = InStr(StrIn, LookFor)
Do While intOK = 0
strFound = Left(strFound, Len(strFound) - intX)
intOK = InStr(StrIn, strFound)
Loop

FindText = strFound


Jerry Whittle said:
PARAMETERS [Enter Name] Text ( 255 );
SELECT YourTable.*
FROM YourTable
WHERE YourTable.HIER=[Enter Name]
OR YourTable.HIER)=Left([Enter Name],5
OR YourTable.HIER)=Left([Enter Name],4 ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Alex said:
Hi this is a repost actually.

I'm not sure if this can be done in a query by creating a function first.
Here's an example: I need to find NICKJM in the "HIER" field. If there is
none, then it would look for NICKJ, and if there is no value it would look
for NICK. It will do this until it has matched. Now the value NICKJM is an
example and it can be anything; MMHGA, LKEDK. Once the value is found in
Hier it will place the need to find field in the new field. Is this poosible
to do in a
query?

NICKJM
Click to expand...
Click to expand...
 
A function might do what you want, but I can't think of a way to do this in
SQL.

The following UNTESTED code may work for you.

Function fFirstMatch(strFieldValue As String, strFind As String) As String
'
'Note that declaring the arguments as strings means that you can't pass in
'Null values. If that is important then you will need to change the first
'argument to a variant. IF you expect the second argument to be null at
times
'you will have to test for that before stepping throught the loop or
'add a zerolengthstring to the argument before doing the loop
'

Dim I As Integer

For I = Len(strFind) To 1 Step -1
If strFieldValue = Left(strFind, I) Then
fFirstMatch = Left(strFind, I)
Exit For
End If
Next I

End Function

You could use that in a query.

SELECT fFirstMatch([Hier] & "", "NICKJ") as MatchedValue
FROM [Your Table]


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Alex said:
Hi Jerry

Thank you for your response. This doesn't appear to be working. [Enter
Name] is a field from another table. Also, once value is found in the
Hier
field, it will no longer be necessary to search the remaining left[Enter
Name]#. So if say NICKJ is the first value in the [Enter Name] field
(Enter
name is going to have a lot of different values). In the Hier field there
is
no NICKJ but a NICK, so in the new created field the NICKJ will be placed.
Even though there might be a value NIC, in the hier field, it won't place
NICKJ in the new field there because it has already found NICK. This is
why
I'm thinking a function should be created similar to the one listed below.
This isn't work quite like I want though. Any modifcation to the function
or
a new function would be appreciated.

Function FindText(StrIn As String, LookFor As String) As String
Dim intOK As Integer
Dim strFound As String
strFound = LookFor
Dim intX As Integer
intX = 1

intOK = InStr(StrIn, LookFor)
Do While intOK = 0
strFound = Left(strFound, Len(strFound) - intX)
intOK = InStr(StrIn, strFound)
Loop

FindText = strFound


Jerry Whittle said:
PARAMETERS [Enter Name] Text ( 255 );
SELECT YourTable.*
FROM YourTable
WHERE YourTable.HIER=[Enter Name]
OR YourTable.HIER)=Left([Enter Name],5
OR YourTable.HIER)=Left([Enter Name],4 ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Alex said:
Hi this is a repost actually.

I'm not sure if this can be done in a query by creating a function
first.
Here's an example: I need to find NICKJM in the "HIER" field. If there
is
none, then it would look for NICKJ, and if there is no value it would
look
for NICK. It will do this until it has matched. Now the value NICKJM is
an
example and it can be anything; MMHGA, LKEDK. Once the value is found
in
Hier it will place the need to find field in the new field. Is this
poosible
to do in a
query?

NICKJM
Click to expand...
Click to expand...
Click to expand...
 
Ok This appears to be working if the value is exactly what I'm looking for.
Now can you provide the code with the loop. Thanks again

John Spencer said:
A function might do what you want, but I can't think of a way to do this in
SQL.

The following UNTESTED code may work for you.

Function fFirstMatch(strFieldValue As String, strFind As String) As String
'
'Note that declaring the arguments as strings means that you can't pass in
'Null values. If that is important then you will need to change the first
'argument to a variant. IF you expect the second argument to be null at
times
'you will have to test for that before stepping throught the loop or
'add a zerolengthstring to the argument before doing the loop
'

Dim I As Integer

For I = Len(strFind) To 1 Step -1
If strFieldValue = Left(strFind, I) Then
fFirstMatch = Left(strFind, I)
Exit For
End If
Next I

End Function

You could use that in a query.

SELECT fFirstMatch([Hier] & "", "NICKJ") as MatchedValue
FROM [Your Table]


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Alex said:
Hi Jerry

Thank you for your response. This doesn't appear to be working. [Enter
Name] is a field from another table. Also, once value is found in the
Hier
field, it will no longer be necessary to search the remaining left[Enter
Name]#. So if say NICKJ is the first value in the [Enter Name] field
(Enter
name is going to have a lot of different values). In the Hier field there
is
no NICKJ but a NICK, so in the new created field the NICKJ will be placed.
Even though there might be a value NIC, in the hier field, it won't place
NICKJ in the new field there because it has already found NICK. This is
why
I'm thinking a function should be created similar to the one listed below.
This isn't work quite like I want though. Any modifcation to the function
or
a new function would be appreciated.

Function FindText(StrIn As String, LookFor As String) As String
Dim intOK As Integer
Dim strFound As String
strFound = LookFor
Dim intX As Integer
intX = 1

intOK = InStr(StrIn, LookFor)
Do While intOK = 0
strFound = Left(strFound, Len(strFound) - intX)
intOK = InStr(StrIn, strFound)
Loop

FindText = strFound


Jerry Whittle said:
PARAMETERS [Enter Name] Text ( 255 );
SELECT YourTable.*
FROM YourTable
WHERE YourTable.HIER=[Enter Name]
OR YourTable.HIER)=Left([Enter Name],5
OR YourTable.HIER)=Left([Enter Name],4 ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hi this is a repost actually.

I'm not sure if this can be done in a query by creating a function
first.
Here's an example: I need to find NICKJM in the "HIER" field. If there
is
none, then it would look for NICKJ, and if there is no value it would
look
for NICK. It will do this until it has matched. Now the value NICKJM is
an
example and it can be anything; MMHGA, LKEDK. Once the value is found
in
Hier it will place the need to find field in the new field. Is this
poosible
to do in a
query?

NICKJM
Click to expand...
Click to expand...
Click to expand...
 
I'm sorry, we are obviously not understanding what you are attempting to do.

The function will go through all the records and put a value into a field
based on the two values you have.

If you want to only get records that have a match of some kind and you want
to specify the match value we can modify the function to return NULLs when
it gets no match and the match value when it does get a match.

Function fFirstMatch(FieldValue As Variant, strFind As String) As Variant

Dim I As Integer
Dim vReturn as Variant
vReturn = Null

IF Len(FieldValue & "") > 0 then
For I = Len(strFind) To 1 Step -1
If strFieldValue = Left(strFind, I) Then
vReturn= Left(strFind, I)
Exit For
End If
Next I
End IF

End Function

The SQL for a query then might look like the following.

SELECT [Your Table].*
, fFirstMatch([Hier], [Match What] ) as MatchValue
FROM [Your Table]
WHERE fFirstMatch([Hier], [Match What] ) is Not Null


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Alex said:
Ok This appears to be working if the value is exactly what I'm looking
for.
Now can you provide the code with the loop. Thanks again

John Spencer said:
A function might do what you want, but I can't think of a way to do this
in
SQL.

The following UNTESTED code may work for you.

Function fFirstMatch(strFieldValue As String, strFind As String) As
String
'
'Note that declaring the arguments as strings means that you can't pass
in
'Null values. If that is important then you will need to change the
first
'argument to a variant. IF you expect the second argument to be null at
times
'you will have to test for that before stepping throught the loop or
'add a zerolengthstring to the argument before doing the loop
'

Dim I As Integer

For I = Len(strFind) To 1 Step -1
If strFieldValue = Left(strFind, I) Then
fFirstMatch = Left(strFind, I)
Exit For
End If
Next I

End Function

You could use that in a query.

SELECT fFirstMatch([Hier] & "", "NICKJ") as MatchedValue
FROM [Your Table]


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Alex said:
Hi Jerry

Thank you for your response. This doesn't appear to be working.
[Enter
Name] is a field from another table. Also, once value is found in the
Hier
field, it will no longer be necessary to search the remaining
left[Enter
Name]#. So if say NICKJ is the first value in the [Enter Name] field
(Enter
name is going to have a lot of different values). In the Hier field
there
is
no NICKJ but a NICK, so in the new created field the NICKJ will be
placed.
Even though there might be a value NIC, in the hier field, it won't
place
NICKJ in the new field there because it has already found NICK. This
is
why
I'm thinking a function should be created similar to the one listed
below.
This isn't work quite like I want though. Any modifcation to the
function
or
a new function would be appreciated.

Function FindText(StrIn As String, LookFor As String) As String
Dim intOK As Integer
Dim strFound As String
strFound = LookFor
Dim intX As Integer
intX = 1

intOK = InStr(StrIn, LookFor)
Do While intOK = 0
strFound = Left(strFound, Len(strFound) - intX)
intOK = InStr(StrIn, strFound)
Loop

FindText = strFound


:

PARAMETERS [Enter Name] Text ( 255 );
SELECT YourTable.*
FROM YourTable
WHERE YourTable.HIER=[Enter Name]
OR YourTable.HIER)=Left([Enter Name],5
OR YourTable.HIER)=Left([Enter Name],4 ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hi this is a repost actually.

I'm not sure if this can be done in a query by creating a function
first.
Here's an example: I need to find NICKJM in the "HIER" field. If
there
is
none, then it would look for NICKJ, and if there is no value it
would
look
for NICK. It will do this until it has matched. Now the value NICKJM
is
an
example and it can be anything; MMHGA, LKEDK. Once the value is
found
in
Hier it will place the need to find field in the new field. Is this
poosible
to do in a
query?

NICKJM
Click to expand...
Click to expand...
Click to expand...
 
John

On both codes, the matchvalue has come blank. This is exactly the values I
have and are looking for and this is how I have the sql (two different ways)

1st scenario
Searching for NMIKL in the field Hier. Hier has 5 values. NMIKLN, NMIAK,
MJHAOO,
FATHA, and NMIKH. What should appear is

Hier MatchValue
NMIKLN NMIKL

SQL
SELECT tblTest.*, fFirstMatch([Hier],"NMIKL") AS MatchValue
FROM tblTest;



2nd scenario
Here Match What field from tblTest2 has NMIKL, and MJHAL. So it should return
Hier MatchValue
NMIKLN NMIKL
MJHAOO MJHAL

SQL
SELECT [tblTest].*
, fFirstMatch([Hier], [tblTest2].[Match What] ) as MatchValue
FROM [tblTest]
WHERE fFirstMatch([Hier], [tblTest2].[Match What] ) is Not Null

On both scenarios I have them returning the Hier Values as is and the Match
Value as null (this is if I didn't put the not null)

-Thank you for your patience and support
John Spencer said:
I'm sorry, we are obviously not understanding what you are attempting to do.

The function will go through all the records and put a value into a field
based on the two values you have.

If you want to only get records that have a match of some kind and you want
to specify the match value we can modify the function to return NULLs when
it gets no match and the match value when it does get a match.

Function fFirstMatch(FieldValue As Variant, strFind As String) As Variant

Dim I As Integer
Dim vReturn as Variant
vReturn = Null

IF Len(FieldValue & "") > 0 then
For I = Len(strFind) To 1 Step -1
If strFieldValue = Left(strFind, I) Then
vReturn= Left(strFind, I)
Exit For
End If
Next I
End IF

End Function

The SQL for a query then might look like the following.

SELECT [Your Table].*
, fFirstMatch([Hier], [Match What] ) as MatchValue
FROM [Your Table]
WHERE fFirstMatch([Hier], [Match What] ) is Not Null


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Alex said:
Ok This appears to be working if the value is exactly what I'm looking
for.
Now can you provide the code with the loop. Thanks again

John Spencer said:
A function might do what you want, but I can't think of a way to do this
in
SQL.

The following UNTESTED code may work for you.

Function fFirstMatch(strFieldValue As String, strFind As String) As
String
'
'Note that declaring the arguments as strings means that you can't pass
in
'Null values. If that is important then you will need to change the
first
'argument to a variant. IF you expect the second argument to be null at
times
'you will have to test for that before stepping throught the loop or
'add a zerolengthstring to the argument before doing the loop
'

Dim I As Integer

For I = Len(strFind) To 1 Step -1
If strFieldValue = Left(strFind, I) Then
fFirstMatch = Left(strFind, I)
Exit For
End If
Next I

End Function

You could use that in a query.

SELECT fFirstMatch([Hier] & "", "NICKJ") as MatchedValue
FROM [Your Table]


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi Jerry

Thank you for your response. This doesn't appear to be working.
[Enter
Name] is a field from another table. Also, once value is found in the
Hier
field, it will no longer be necessary to search the remaining
left[Enter
Name]#. So if say NICKJ is the first value in the [Enter Name] field
(Enter
name is going to have a lot of different values). In the Hier field
there
is
no NICKJ but a NICK, so in the new created field the NICKJ will be
placed.
Even though there might be a value NIC, in the hier field, it won't
place
NICKJ in the new field there because it has already found NICK. This
is
why
I'm thinking a function should be created similar to the one listed
below.
This isn't work quite like I want though. Any modifcation to the
function
or
a new function would be appreciated.

Function FindText(StrIn As String, LookFor As String) As String
Dim intOK As Integer
Dim strFound As String
strFound = LookFor
Dim intX As Integer
intX = 1

intOK = InStr(StrIn, LookFor)
Do While intOK = 0
strFound = Left(strFound, Len(strFound) - intX)
intOK = InStr(StrIn, strFound)
Loop

FindText = strFound


:

PARAMETERS [Enter Name] Text ( 255 );
SELECT YourTable.*
FROM YourTable
WHERE YourTable.HIER=[Enter Name]
OR YourTable.HIER)=Left([Enter Name],5
OR YourTable.HIER)=Left([Enter Name],4 ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hi this is a repost actually.

I'm not sure if this can be done in a query by creating a function
first.
Here's an example: I need to find NICKJM in the "HIER" field. If
there
is
none, then it would look for NICKJ, and if there is no value it
would
look
for NICK. It will do this until it has matched. Now the value NICKJM
is
an
example and it can be anything; MMHGA, LKEDK. Once the value is
found
in
Hier it will place the need to find field in the new field. Is this
poosible
to do in a
query?

NICKJM
Click to expand...
Click to expand...
Click to expand...
 
Hi Alex,

It would be helpful if you explained what was wrong with the three
suggestions you got in microsoft.public.access.modulescoding on 8
February. Anyway, here's another, which assumes that the partial match
can occur anywhere in the field rather than just at the beginning. It
needs a VBA function that returns the number of characters in the
"match" string (e.g. NICKJM) that can be matched in a target string:

Function NumMatchingCharacters( _
ByVal SearchString As Variant, _
ByVal MatchString As String) As Long

If IsNull(SearchString) Then
NumMatchingCharacters = 0
Exit Function
End If

If Len(MatchString) > Len(SearchString) Then
MatchString = Left(MatchString, Len(SearchString))
End If

Do While Len(MatchString) > 0
If InStr(SearchString, MatchString) > 0 Then
Exit Do
Else
MatchString = Left(MatchString, Len(MatchString) - 1)
End If
Loop

NumMatchingCharacters = Len(MatchString)

End Function

Then you can use this in a rather slow query and subquery, e.g.:

PARAMETERS [Match] TEXT (10);
SELECT HIER, Match
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) = (
SELECT MAX(NumMatchingCharacters(HIER, Match))
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) > 0
)
;
 
So you want is not an exact match but a begins with match. So the function
would need to be rewritten. The siimplest modification would be to use

If Left(strFieldValue,I) = Left(strFind,I) Then

Function fFirstMatch(FieldValue As Variant, strFind As String) As Variant
Dim I As Integer
Dim vReturn as Variant
vReturn = Null

IF Len(FieldValue & "") > 0 then
For I = Len(strFind) To 1 Step -1
If Left(strFieldValue,I) = Left(strFind, I) Then
vReturn= Left(strFind, I)
Exit For
End If
Next I
End IF

End Function

I've been puzzling over this some more and have a few questions for you.
WHERE are you intending to use the results of this query? In a report? As
the source of a form, listbox, or combobox?

Given NMIKLN, NMIAK, MJHAOO, FATHA, and NMIKH and a match string of NMIKH
which of those five would you want returned? The solution I proposed would
return
Field Match Value
NMIKLN NMIK
NMIKH NMIKH
NMIAK NMI

What I think you might want is only one record returned.
NMIKH NMIKH

That would be possible if you were searching for one constant value (your
first scenario), but your second scenario becomes more complex

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Alex said:
John

On both codes, the matchvalue has come blank. This is exactly the values
I
have and are looking for and this is how I have the sql (two different
ways)

1st scenario
Searching for NMIKL in the field Hier. Hier has 5 values. NMIKLN, NMIAK,
MJHAOO,
FATHA, and NMIKH. What should appear is

Hier MatchValue
NMIKLN NMIKL

SQL
SELECT tblTest.*, fFirstMatch([Hier],"NMIKL") AS MatchValue
FROM tblTest;



2nd scenario
Here Match What field from tblTest2 has NMIKL, and MJHAL. So it should
return
Hier MatchValue
NMIKLN NMIKL
MJHAOO MJHAL

SQL
SELECT [tblTest].*
, fFirstMatch([Hier], [tblTest2].[Match What] ) as MatchValue
FROM [tblTest]
WHERE fFirstMatch([Hier], [tblTest2].[Match What] ) is Not Null

On both scenarios I have them returning the Hier Values as is and the
Match
Value as null (this is if I didn't put the not null)

-Thank you for your patience and support
John Spencer said:
I'm sorry, we are obviously not understanding what you are attempting to
do.

The function will go through all the records and put a value into a field
based on the two values you have.

If you want to only get records that have a match of some kind and you
want
to specify the match value we can modify the function to return NULLs
when
it gets no match and the match value when it does get a match.

Function fFirstMatch(FieldValue As Variant, strFind As String) As Variant

Dim I As Integer
Dim vReturn as Variant
vReturn = Null

IF Len(FieldValue & "") > 0 then
For I = Len(strFind) To 1 Step -1
If strFieldValue = Left(strFind, I) Then
vReturn= Left(strFind, I)
Exit For
End If
Next I
End IF

End Function

The SQL for a query then might look like the following.

SELECT [Your Table].*
, fFirstMatch([Hier], [Match What] ) as MatchValue
FROM [Your Table]
WHERE fFirstMatch([Hier], [Match What] ) is Not Null


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Alex said:
Ok This appears to be working if the value is exactly what I'm looking
for.
Now can you provide the code with the loop. Thanks again

:

A function might do what you want, but I can't think of a way to do
this
in
SQL.

The following UNTESTED code may work for you.

Function fFirstMatch(strFieldValue As String, strFind As String) As
String
'
'Note that declaring the arguments as strings means that you can't
pass
in
'Null values. If that is important then you will need to change the
first
'argument to a variant. IF you expect the second argument to be null
at
times
'you will have to test for that before stepping throught the loop or
'add a zerolengthstring to the argument before doing the loop
'

Dim I As Integer

For I = Len(strFind) To 1 Step -1
If strFieldValue = Left(strFind, I) Then
fFirstMatch = Left(strFind, I)
Exit For
End If
Next I

End Function

You could use that in a query.

SELECT fFirstMatch([Hier] & "", "NICKJ") as MatchedValue
FROM [Your Table]


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi Jerry

Thank you for your response. This doesn't appear to be working.
[Enter
Name] is a field from another table. Also, once value is found in
the
Hier
field, it will no longer be necessary to search the remaining
left[Enter
Name]#. So if say NICKJ is the first value in the [Enter Name]
field
(Enter
name is going to have a lot of different values). In the Hier field
there
is
no NICKJ but a NICK, so in the new created field the NICKJ will be
placed.
Even though there might be a value NIC, in the hier field, it won't
place
NICKJ in the new field there because it has already found NICK.
This
is
why
I'm thinking a function should be created similar to the one listed
below.
This isn't work quite like I want though. Any modifcation to the
function
or
a new function would be appreciated.

Function FindText(StrIn As String, LookFor As String) As String
Dim intOK As Integer
Dim strFound As String
strFound = LookFor
Dim intX As Integer
intX = 1

intOK = InStr(StrIn, LookFor)
Do While intOK = 0
strFound = Left(strFound, Len(strFound) - intX)
intOK = InStr(StrIn, strFound)
Loop

FindText = strFound


:

PARAMETERS [Enter Name] Text ( 255 );
SELECT YourTable.*
FROM YourTable
WHERE YourTable.HIER=[Enter Name]
OR YourTable.HIER)=Left([Enter Name],5
OR YourTable.HIER)=Left([Enter Name],4 ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hi this is a repost actually.

I'm not sure if this can be done in a query by creating a
function
first.
Here's an example: I need to find NICKJM in the "HIER" field. If
there
is
none, then it would look for NICKJ, and if there is no value it
would
look
for NICK. It will do this until it has matched. Now the value
NICKJM
is
an
example and it can be anything; MMHGA, LKEDK. Once the value is
found
in
Hier it will place the need to find field in the new field. Is
this
poosible
to do in a
query?

NICKJM
Click to expand...
Click to expand...
Click to expand...
 
The query would just be copied to an excel spreadsheet. Orginally this was
done manually, it was very time consuming. You are correct that it should
only return one value unless there was two NMIKH there. It shouldn't return
below.

Field Match Value
NMIKLN NMIK
NMIAK NMI
Click to expand...

But say NMIKH wasn't in the field then it should return as below. The match
value should be NMIKH even though NMIK was found, cause orginally it started
with NMIKH..but I can resolve ths simply by adding a new field that was used
to search. SO no biggie on that.
Field Match Value
NMIKLN NMIKH
Click to expand...

So, I guess the issue is once if finds with the certain #of characters in
this case 5. It wouldnt have to return with a value of 4 characters.

You can see how this can be time consuming when u have 1000 records and have
like 1000 different values to mix and match cause it usually is never the
exact match.

John Spencer said:
So you want is not an exact match but a begins with match. So the function
would need to be rewritten. The siimplest modification would be to use

If Left(strFieldValue,I) = Left(strFind,I) Then

Function fFirstMatch(FieldValue As Variant, strFind As String) As Variant
Dim I As Integer
Dim vReturn as Variant
vReturn = Null

IF Len(FieldValue & "") > 0 then
For I = Len(strFind) To 1 Step -1
If Left(strFieldValue,I) = Left(strFind, I) Then
vReturn= Left(strFind, I)
Exit For
End If
Next I
End IF

End Function

I've been puzzling over this some more and have a few questions for you.
WHERE are you intending to use the results of this query? In a report? As
the source of a form, listbox, or combobox?

Given NMIKLN, NMIAK, MJHAOO, FATHA, and NMIKH and a match string of NMIKH
which of those five would you want returned? The solution I proposed would
return
Field Match Value
NMIKLN NMIK
NMIKH NMIKH
NMIAK NMI

What I think you might want is only one record returned.
NMIKH NMIKH

That would be possible if you were searching for one constant value (your
first scenario), but your second scenario becomes more complex

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Alex said:
John

On both codes, the matchvalue has come blank. This is exactly the values
I
have and are looking for and this is how I have the sql (two different
ways)

1st scenario
Searching for NMIKL in the field Hier. Hier has 5 values. NMIKLN, NMIAK,
MJHAOO,
FATHA, and NMIKH. What should appear is

Hier MatchValue
NMIKLN NMIKL

SQL
SELECT tblTest.*, fFirstMatch([Hier],"NMIKL") AS MatchValue
FROM tblTest;



2nd scenario
Here Match What field from tblTest2 has NMIKL, and MJHAL. So it should
return
Hier MatchValue
NMIKLN NMIKL
MJHAOO MJHAL

SQL
SELECT [tblTest].*
, fFirstMatch([Hier], [tblTest2].[Match What] ) as MatchValue
FROM [tblTest]
WHERE fFirstMatch([Hier], [tblTest2].[Match What] ) is Not Null

On both scenarios I have them returning the Hier Values as is and the
Match
Value as null (this is if I didn't put the not null)

-Thank you for your patience and support
John Spencer said:
I'm sorry, we are obviously not understanding what you are attempting to
do.

The function will go through all the records and put a value into a field
based on the two values you have.

If you want to only get records that have a match of some kind and you
want
to specify the match value we can modify the function to return NULLs
when
it gets no match and the match value when it does get a match.

Function fFirstMatch(FieldValue As Variant, strFind As String) As Variant

Dim I As Integer
Dim vReturn as Variant
vReturn = Null

IF Len(FieldValue & "") > 0 then
For I = Len(strFind) To 1 Step -1
If strFieldValue = Left(strFind, I) Then
vReturn= Left(strFind, I)
Exit For
End If
Next I
End IF

End Function

The SQL for a query then might look like the following.

SELECT [Your Table].*
, fFirstMatch([Hier], [Match What] ) as MatchValue
FROM [Your Table]
WHERE fFirstMatch([Hier], [Match What] ) is Not Null


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ok This appears to be working if the value is exactly what I'm looking
for.
Now can you provide the code with the loop. Thanks again

:

A function might do what you want, but I can't think of a way to do
this
in
SQL.

The following UNTESTED code may work for you.

Function fFirstMatch(strFieldValue As String, strFind As String) As
String
'
'Note that declaring the arguments as strings means that you can't
pass
in
'Null values. If that is important then you will need to change the
first
'argument to a variant. IF you expect the second argument to be null
at
times
'you will have to test for that before stepping throught the loop or
'add a zerolengthstring to the argument before doing the loop
'

Dim I As Integer

For I = Len(strFind) To 1 Step -1
If strFieldValue = Left(strFind, I) Then
fFirstMatch = Left(strFind, I)
Exit For
End If
Next I

End Function

You could use that in a query.

SELECT fFirstMatch([Hier] & "", "NICKJ") as MatchedValue
FROM [Your Table]


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi Jerry

Thank you for your response. This doesn't appear to be working.
[Enter
Name] is a field from another table. Also, once value is found in
the
Hier
field, it will no longer be necessary to search the remaining
left[Enter
Name]#. So if say NICKJ is the first value in the [Enter Name]
field
(Enter
name is going to have a lot of different values). In the Hier field
there
is
no NICKJ but a NICK, so in the new created field the NICKJ will be
placed.
Even though there might be a value NIC, in the hier field, it won't
place
NICKJ in the new field there because it has already found NICK.
This
is
why
I'm thinking a function should be created similar to the one listed
below.
This isn't work quite like I want though. Any modifcation to the
function
or
a new function would be appreciated.

Function FindText(StrIn As String, LookFor As String) As String
Dim intOK As Integer
Dim strFound As String
strFound = LookFor
Dim intX As Integer
intX = 1

intOK = InStr(StrIn, LookFor)
Do While intOK = 0
strFound = Left(strFound, Len(strFound) - intX)
intOK = InStr(StrIn, strFound)
Loop

FindText = strFound


:

PARAMETERS [Enter Name] Text ( 255 );
SELECT YourTable.*
FROM YourTable
WHERE YourTable.HIER=[Enter Name]
OR YourTable.HIER)=Left([Enter Name],5
OR YourTable.HIER)=Left([Enter Name],4 ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hi this is a repost actually.

I'm not sure if this can be done in a query by creating a
function
first.
Here's an example: I need to find NICKJM in the "HIER" field. If
there
is
none, then it would look for NICKJ, and if there is no value it
would
look
for NICK. It will do this until it has matched. Now the value
NICKJM
is
an
example and it can be anything; MMHGA, LKEDK. Once the value is
found
in
Hier it will place the need to find field in the new field. Is
this
poosible
to do in a
query?

NICKJM
Click to expand...
Click to expand...
Click to expand...
 
John thank you for your time, however the partial match should be at the
beginning of the match. Please see my other posts and replies to John
Spencer. He knows exactly what I'm looking for.

John Nurick said:
Hi Alex,

It would be helpful if you explained what was wrong with the three
suggestions you got in microsoft.public.access.modulescoding on 8
February. Anyway, here's another, which assumes that the partial match
can occur anywhere in the field rather than just at the beginning. It
needs a VBA function that returns the number of characters in the
"match" string (e.g. NICKJM) that can be matched in a target string:

Function NumMatchingCharacters( _
ByVal SearchString As Variant, _
ByVal MatchString As String) As Long

If IsNull(SearchString) Then
NumMatchingCharacters = 0
Exit Function
End If

If Len(MatchString) > Len(SearchString) Then
MatchString = Left(MatchString, Len(SearchString))
End If

Do While Len(MatchString) > 0
If InStr(SearchString, MatchString) > 0 Then
Exit Do
Else
MatchString = Left(MatchString, Len(MatchString) - 1)
End If
Loop

NumMatchingCharacters = Len(MatchString)

End Function

Then you can use this in a rather slow query and subquery, e.g.:

PARAMETERS [Match] TEXT (10);
SELECT HIER, Match
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) = (
SELECT MAX(NumMatchingCharacters(HIER, Match))
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) > 0
)
;




I'm not sure if this can be done in a query by creating a function first.
Here's an example: I need to find NICKJM in the "HIER" field. If there is
none, then it would look for NICKJ, and if there is no value it would look
for NICK. It will do this until it has matched. Now the value NICKJM is an
example and it can be anything; MMHGA, LKEDK. Once the value is found in
Hier it will place the need to find field in the new field. Is this poosible
to do in a query?
Click to expand...
Click to expand...
 
In that case just change

If InStr(SearchString, MatchString) > 0 Then

in the function to

If InStr(SearchString, MatchString) = 1 Then



John thank you for your time, however the partial match should be at the
beginning of the match. Please see my other posts and replies to John
Spencer. He knows exactly what I'm looking for.

John Nurick said:
Hi Alex,

It would be helpful if you explained what was wrong with the three
suggestions you got in microsoft.public.access.modulescoding on 8
February. Anyway, here's another, which assumes that the partial match
can occur anywhere in the field rather than just at the beginning. It
needs a VBA function that returns the number of characters in the
"match" string (e.g. NICKJM) that can be matched in a target string:

Function NumMatchingCharacters( _
ByVal SearchString As Variant, _
ByVal MatchString As String) As Long

If IsNull(SearchString) Then
NumMatchingCharacters = 0
Exit Function
End If

If Len(MatchString) > Len(SearchString) Then
MatchString = Left(MatchString, Len(SearchString))
End If

Do While Len(MatchString) > 0
If InStr(SearchString, MatchString) > 0 Then
Exit Do
Else
MatchString = Left(MatchString, Len(MatchString) - 1)
End If
Loop

NumMatchingCharacters = Len(MatchString)

End Function

Then you can use this in a rather slow query and subquery, e.g.:

PARAMETERS [Match] TEXT (10);
SELECT HIER, Match
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) = (
SELECT MAX(NumMatchingCharacters(HIER, Match))
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) > 0
)
;




I'm not sure if this can be done in a query by creating a function first.
Here's an example: I need to find NICKJM in the "HIER" field. If there is
none, then it would look for NICKJ, and if there is no value it would look
for NICK. It will do this until it has matched. Now the value NICKJM is an
example and it can be anything; MMHGA, LKEDK. Once the value is found in
Hier it will place the need to find field in the new field. Is this poosible
to do in a query?
Click to expand...
Click to expand...
Click to expand...
 
John almost there. I do have a question about how it's set up below though.
Match is actually going to be another field from a table, that we are trying
to match so no need for parameters. I understand the MAX number but I need
another field to show what we were trying to match. So I am assuming I need
to do a left join first to show all the values of HIER. and then see if the
values in the Match field are there? Not sure how to write it out.

PARAMETERS [Match] TEXT (10);
SELECT HIER, Match
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) = (
SELECT MAX(NumMatchingCharacters(HIER, Match))
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) > 0

John Nurick said:
In that case just change

If InStr(SearchString, MatchString) > 0 Then

in the function to

If InStr(SearchString, MatchString) = 1 Then



John thank you for your time, however the partial match should be at the
beginning of the match. Please see my other posts and replies to John
Spencer. He knows exactly what I'm looking for.

John Nurick said:
Hi Alex,

It would be helpful if you explained what was wrong with the three
suggestions you got in microsoft.public.access.modulescoding on 8
February. Anyway, here's another, which assumes that the partial match
can occur anywhere in the field rather than just at the beginning. It
needs a VBA function that returns the number of characters in the
"match" string (e.g. NICKJM) that can be matched in a target string:

Function NumMatchingCharacters( _
ByVal SearchString As Variant, _
ByVal MatchString As String) As Long

If IsNull(SearchString) Then
NumMatchingCharacters = 0
Exit Function
End If

If Len(MatchString) > Len(SearchString) Then
MatchString = Left(MatchString, Len(SearchString))
End If

Do While Len(MatchString) > 0
If InStr(SearchString, MatchString) > 0 Then
Exit Do
Else
MatchString = Left(MatchString, Len(MatchString) - 1)
End If
Loop

NumMatchingCharacters = Len(MatchString)

End Function

Then you can use this in a rather slow query and subquery, e.g.:

PARAMETERS [Match] TEXT (10);
SELECT HIER, Match
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) = (
SELECT MAX(NumMatchingCharacters(HIER, Match))
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) > 0
)
;




On Wed, 28 Feb 2007 06:43:44 -0800, Alex

I'm not sure if this can be done in a query by creating a function first.
Here's an example: I need to find NICKJM in the "HIER" field. If there is
none, then it would look for NICKJ, and if there is no value it would look
for NICK. It will do this until it has matched. Now the value NICKJM is an
example and it can be anything; MMHGA, LKEDK. Once the value is found in
Hier it will place the need to find field in the new field. Is this poosible
to do in a query?
Click to expand...
Click to expand...
Click to expand...
 
This works in my sample database - but *very* slowly. It requires
further slight change to the VBA function; see below.

SELECT B.Field6, A.ID, A.Postcode,
NumMatchingCharacters(A.PostCode, B.Field6) As MatchLength
FROM AddrNew AS A INNER JOIN Asterisk AS B
ON Left(B.Field6, 1) = Left(A.Postcode, 1)
WHERE NumMatchingCharacters(A.PostCode, B.Field6) = (
SELECT MAX(NumMatchingCharacters(C.Postcode, B.Field6))
FROM AddrNew AS C
WHERE NumMatchingCharacters(C.PostCode, B.Field6) > 0
);

[AddrNew] is the main table, and [Postcode] corresponds to your [HIER]
field. [Asterisk] is the table that contains the values you want to find
matches for, in [Field6]. The [MatchLength] calculated field is only
there to show what's going on.

The INNER JOIN is an attempt to speed things up by excluding pairs of
records that can't possibly match; I'm not sure that it's actually any
faster than using a Cartesian join
FROM AddrNew AS A, Asterisk AS B
WHERE B.Field6 IS NOT NULL
AND NumMatchingCharacters(...

Maybe a real SQL expert will see this and show us a better way.

One thing I'm not sure about. Here's the output from the query in my
sample database:

Field6 ID Postcode MatchLength
NICKJHT 1 NICKJHT 7
ROSEBER 18 ROSEBER40 7
JOHNSXRES 25 JOHNSXTERES 6
JOTRESG 25 JOHNSXTERES 2

Note that two values in Field6 have matched against the same record in
the main table. Is that what you want to happen? If not, what?




Here's the revised NumMatchingCharacters() function: it now handles Null
values better.

Function NumMatchingCharacters( _
ByVal SearchString As Variant, _
ByVal MatchString As Variant) As Long

If IsNull(SearchString) Or IsNull(MatchString) Then
NumMatchingCharacters = 0
Exit Function
End If

If Len(MatchString) > Len(SearchString) Then
MatchString = Left(MatchString, Len(SearchString))
End If

Do While Len(MatchString) > 0
If InStr(SearchString, MatchString) = 1 Then
Exit Do
Else
MatchString = Left(MatchString, Len(MatchString) - 1)
End If
Loop

NumMatchingCharacters = Nz(Len(MatchString), 0)

End Function







John almost there. I do have a question about how it's set up below though.
Match is actually going to be another field from a table, that we are trying
to match so no need for parameters. I understand the MAX number but I need
another field to show what we were trying to match. So I am assuming I need
to do a left join first to show all the values of HIER. and then see if the
values in the Match field are there? Not sure how to write it out.

PARAMETERS [Match] TEXT (10);
SELECT HIER, Match
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) = (
SELECT MAX(NumMatchingCharacters(HIER, Match))
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) > 0

John Nurick said:
In that case just change

If InStr(SearchString, MatchString) > 0 Then

in the function to

If InStr(SearchString, MatchString) = 1 Then



John thank you for your time, however the partial match should be at the
beginning of the match. Please see my other posts and replies to John
Spencer. He knows exactly what I'm looking for.

:

Hi Alex,

It would be helpful if you explained what was wrong with the three
suggestions you got in microsoft.public.access.modulescoding on 8
February. Anyway, here's another, which assumes that the partial match
can occur anywhere in the field rather than just at the beginning. It
needs a VBA function that returns the number of characters in the
"match" string (e.g. NICKJM) that can be matched in a target string:

Function NumMatchingCharacters( _
ByVal SearchString As Variant, _
ByVal MatchString As String) As Long

If IsNull(SearchString) Then
NumMatchingCharacters = 0
Exit Function
End If

If Len(MatchString) > Len(SearchString) Then
MatchString = Left(MatchString, Len(SearchString))
End If

Do While Len(MatchString) > 0
If InStr(SearchString, MatchString) > 0 Then
Exit Do
Else
MatchString = Left(MatchString, Len(MatchString) - 1)
End If
Loop

NumMatchingCharacters = Len(MatchString)

End Function

Then you can use this in a rather slow query and subquery, e.g.:

PARAMETERS [Match] TEXT (10);
SELECT HIER, Match
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) = (
SELECT MAX(NumMatchingCharacters(HIER, Match))
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) > 0
)
;




On Wed, 28 Feb 2007 06:43:44 -0800, Alex

I'm not sure if this can be done in a query by creating a function first.
Here's an example: I need to find NICKJM in the "HIER" field. If there is
none, then it would look for NICKJ, and if there is no value it would look
for NICK. It will do this until it has matched. Now the value NICKJM is an
example and it can be anything; MMHGA, LKEDK. Once the value is found in
Hier it will place the need to find field in the new field. Is this poosible
to do in a query?
Click to expand...
Click to expand...
Click to expand...
 
JOHN YOU ARE THE MAN.

-Thank you so much, this works great.

John Nurick said:
This works in my sample database - but *very* slowly. It requires
further slight change to the VBA function; see below.

SELECT B.Field6, A.ID, A.Postcode,
NumMatchingCharacters(A.PostCode, B.Field6) As MatchLength
FROM AddrNew AS A INNER JOIN Asterisk AS B
ON Left(B.Field6, 1) = Left(A.Postcode, 1)
WHERE NumMatchingCharacters(A.PostCode, B.Field6) = (
SELECT MAX(NumMatchingCharacters(C.Postcode, B.Field6))
FROM AddrNew AS C
WHERE NumMatchingCharacters(C.PostCode, B.Field6) > 0
);

[AddrNew] is the main table, and [Postcode] corresponds to your [HIER]
field. [Asterisk] is the table that contains the values you want to find
matches for, in [Field6]. The [MatchLength] calculated field is only
there to show what's going on.

The INNER JOIN is an attempt to speed things up by excluding pairs of
records that can't possibly match; I'm not sure that it's actually any
faster than using a Cartesian join
FROM AddrNew AS A, Asterisk AS B
WHERE B.Field6 IS NOT NULL
AND NumMatchingCharacters(...

Maybe a real SQL expert will see this and show us a better way.

One thing I'm not sure about. Here's the output from the query in my
sample database:

Field6 ID Postcode MatchLength
NICKJHT 1 NICKJHT 7
ROSEBER 18 ROSEBER40 7
JOHNSXRES 25 JOHNSXTERES 6
JOTRESG 25 JOHNSXTERES 2

Note that two values in Field6 have matched against the same record in
the main table. Is that what you want to happen? If not, what?




Here's the revised NumMatchingCharacters() function: it now handles Null
values better.

Function NumMatchingCharacters( _
ByVal SearchString As Variant, _
ByVal MatchString As Variant) As Long

If IsNull(SearchString) Or IsNull(MatchString) Then
NumMatchingCharacters = 0
Exit Function
End If

If Len(MatchString) > Len(SearchString) Then
MatchString = Left(MatchString, Len(SearchString))
End If

Do While Len(MatchString) > 0
If InStr(SearchString, MatchString) = 1 Then
Exit Do
Else
MatchString = Left(MatchString, Len(MatchString) - 1)
End If
Loop

NumMatchingCharacters = Nz(Len(MatchString), 0)

End Function







John almost there. I do have a question about how it's set up below though.
Match is actually going to be another field from a table, that we are trying
to match so no need for parameters. I understand the MAX number but I need
another field to show what we were trying to match. So I am assuming I need
to do a left join first to show all the values of HIER. and then see if the
values in the Match field are there? Not sure how to write it out.

PARAMETERS [Match] TEXT (10);
SELECT HIER, Match
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) = (
SELECT MAX(NumMatchingCharacters(HIER, Match))
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) > 0

John Nurick said:
In that case just change

If InStr(SearchString, MatchString) > 0 Then

in the function to

If InStr(SearchString, MatchString) = 1 Then



John thank you for your time, however the partial match should be at the
beginning of the match. Please see my other posts and replies to John
Spencer. He knows exactly what I'm looking for.

:

Hi Alex,

It would be helpful if you explained what was wrong with the three
suggestions you got in microsoft.public.access.modulescoding on 8
February. Anyway, here's another, which assumes that the partial match
can occur anywhere in the field rather than just at the beginning. It
needs a VBA function that returns the number of characters in the
"match" string (e.g. NICKJM) that can be matched in a target string:

Function NumMatchingCharacters( _
ByVal SearchString As Variant, _
ByVal MatchString As String) As Long

If IsNull(SearchString) Then
NumMatchingCharacters = 0
Exit Function
End If

If Len(MatchString) > Len(SearchString) Then
MatchString = Left(MatchString, Len(SearchString))
End If

Do While Len(MatchString) > 0
If InStr(SearchString, MatchString) > 0 Then
Exit Do
Else
MatchString = Left(MatchString, Len(MatchString) - 1)
End If
Loop

NumMatchingCharacters = Len(MatchString)

End Function

Then you can use this in a rather slow query and subquery, e.g.:

PARAMETERS [Match] TEXT (10);
SELECT HIER, Match
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) = (
SELECT MAX(NumMatchingCharacters(HIER, Match))
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) > 0
)
;




On Wed, 28 Feb 2007 06:43:44 -0800, Alex

I'm not sure if this can be done in a query by creating a function first.
Here's an example: I need to find NICKJM in the "HIER" field. If there is
none, then it would look for NICKJ, and if there is no value it would look
for NICK. It will do this until it has matched. Now the value NICKJM is an
example and it can be anything; MMHGA, LKEDK. Once the value is found in
Hier it will place the need to find field in the new field. Is this poosible
to do in a query?
Click to expand...
Click to expand...
Click to expand...
 
John said:
Here's the revised NumMatchingCharacters() function: it now handles Null
values better.

Function NumMatchingCharacters( _
ByVal SearchString As Variant, _
ByVal MatchString As Variant) As Long

If IsNull(SearchString) Or IsNull(MatchString) Then
NumMatchingCharacters = 0
Exit Function
End If

If Len(MatchString) > Len(SearchString) Then
MatchString = Left(MatchString, Len(SearchString))
End If

Do While Len(MatchString) > 0
If InStr(SearchString, MatchString) = 1 Then
Exit Do
Else
MatchString = Left(MatchString, Len(MatchString) - 1)
End If
Loop

NumMatchingCharacters = Nz(Len(MatchString), 0)

End Function
Click to expand...


It probably doesn't matter in that kind of query, but, FWIW,
this might be a little faster:

Function NumMatchingCharacters( _
ByVal SearchString As Variant, _
ByVal MatchString As Variant) As Long

Dim MinLen As Long

If IsNull(SearchString) Or IsNull(MatchString) _
Then Exit Function

MinLen = Len(MatchString)
If Len(SearchString) < MinLen _
Then MinLen = Len(SearchString)

For NumMatchingCharacters = MinLen To 1 Step -1
If Left(SearchString, NumMatchingCharacters) _
= Left(MatchString, NumMatchingCharacters) _
Then Exit For
Next

End Function
 
Thanks for the feedback!

BTW, if there is minimum acceptable length of match (e.g. NICKJH matches
NIC but not NI or N), change the second argument of the Left() functions
in the ON condition of the INNER JOIN from 1 to whatever the minimum
length is. This could speed things up significantly.

JOHN YOU ARE THE MAN.

-Thank you so much, this works great.

John Nurick said:
This works in my sample database - but *very* slowly. It requires
further slight change to the VBA function; see below.

SELECT B.Field6, A.ID, A.Postcode,
NumMatchingCharacters(A.PostCode, B.Field6) As MatchLength
FROM AddrNew AS A INNER JOIN Asterisk AS B
ON Left(B.Field6, 1) = Left(A.Postcode, 1)
WHERE NumMatchingCharacters(A.PostCode, B.Field6) = (
SELECT MAX(NumMatchingCharacters(C.Postcode, B.Field6))
FROM AddrNew AS C
WHERE NumMatchingCharacters(C.PostCode, B.Field6) > 0
);

[AddrNew] is the main table, and [Postcode] corresponds to your [HIER]
field. [Asterisk] is the table that contains the values you want to find
matches for, in [Field6]. The [MatchLength] calculated field is only
there to show what's going on.

The INNER JOIN is an attempt to speed things up by excluding pairs of
records that can't possibly match; I'm not sure that it's actually any
faster than using a Cartesian join
FROM AddrNew AS A, Asterisk AS B
WHERE B.Field6 IS NOT NULL
AND NumMatchingCharacters(...

Maybe a real SQL expert will see this and show us a better way.

One thing I'm not sure about. Here's the output from the query in my
sample database:

Field6 ID Postcode MatchLength
NICKJHT 1 NICKJHT 7
ROSEBER 18 ROSEBER40 7
JOHNSXRES 25 JOHNSXTERES 6
JOTRESG 25 JOHNSXTERES 2

Note that two values in Field6 have matched against the same record in
the main table. Is that what you want to happen? If not, what?




Here's the revised NumMatchingCharacters() function: it now handles Null
values better.

Function NumMatchingCharacters( _
ByVal SearchString As Variant, _
ByVal MatchString As Variant) As Long

If IsNull(SearchString) Or IsNull(MatchString) Then
NumMatchingCharacters = 0
Exit Function
End If

If Len(MatchString) > Len(SearchString) Then
MatchString = Left(MatchString, Len(SearchString))
End If

Do While Len(MatchString) > 0
If InStr(SearchString, MatchString) = 1 Then
Exit Do
Else
MatchString = Left(MatchString, Len(MatchString) - 1)
End If
Loop

NumMatchingCharacters = Nz(Len(MatchString), 0)

End Function







John almost there. I do have a question about how it's set up below though.
Match is actually going to be another field from a table, that we are trying
to match so no need for parameters. I understand the MAX number but I need
another field to show what we were trying to match. So I am assuming I need
to do a left join first to show all the values of HIER. and then see if the
values in the Match field are there? Not sure how to write it out.

PARAMETERS [Match] TEXT (10);
SELECT HIER, Match
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) = (
SELECT MAX(NumMatchingCharacters(HIER, Match))
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) > 0

:

In that case just change

If InStr(SearchString, MatchString) > 0 Then

in the function to

If InStr(SearchString, MatchString) = 1 Then



John thank you for your time, however the partial match should be at the
beginning of the match. Please see my other posts and replies to John
Spencer. He knows exactly what I'm looking for.

:

Hi Alex,

It would be helpful if you explained what was wrong with the three
suggestions you got in microsoft.public.access.modulescoding on 8
February. Anyway, here's another, which assumes that the partial match
can occur anywhere in the field rather than just at the beginning. It
needs a VBA function that returns the number of characters in the
"match" string (e.g. NICKJM) that can be matched in a target string:

Function NumMatchingCharacters( _
ByVal SearchString As Variant, _
ByVal MatchString As String) As Long

If IsNull(SearchString) Then
NumMatchingCharacters = 0
Exit Function
End If

If Len(MatchString) > Len(SearchString) Then
MatchString = Left(MatchString, Len(SearchString))
End If

Do While Len(MatchString) > 0
If InStr(SearchString, MatchString) > 0 Then
Exit Do
Else
MatchString = Left(MatchString, Len(MatchString) - 1)
End If
Loop

NumMatchingCharacters = Len(MatchString)

End Function

Then you can use this in a rather slow query and subquery, e.g.:

PARAMETERS [Match] TEXT (10);
SELECT HIER, Match
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) = (
SELECT MAX(NumMatchingCharacters(HIER, Match))
FROM MyTable
WHERE NumMatchingCharacters(HIER, Match) > 0
)
;




On Wed, 28 Feb 2007 06:43:44 -0800, Alex

I'm not sure if this can be done in a query by creating a function first.
Here's an example: I need to find NICKJM in the "HIER" field. If there is
none, then it would look for NICKJ, and if there is no value it would look
for NICK. It will do this until it has matched. Now the value NICKJM is an
example and it can be anything; MMHGA, LKEDK. Once the value is found in
Hier it will place the need to find field in the new field. Is this poosible
to do in a query?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top