Using Soundex

  • Thread starter Thread starter Ivan Debono
  • Start date Start date
I

Ivan Debono

Hi all,

I use MDAC 2.81 & Jet 5. Is it possible to use the SoundEx function in a
query that's executed over ADO?

Thanks,
Ivan
 
Ivan,

Presuming that you are using the Soundex that is freely available, all you
have to do is declare one of your query fields in the following way:

Field:- SoundCoce: Soundex([SurName])

It's that easy.

I hope this helps.

Alastair
 
Hi,

No I don't have and from where do I get it?

Regards,
Ivan

Alastair MacFarlane said:
Ivan,

Presuming that you are using the Soundex that is freely available, all you
have to do is declare one of your query fields in the following way:

Field:- SoundCoce: Soundex([SurName])

It's that easy.

I hope this helps.

Alastair
Ivan Debono said:
Hi all,

I use MDAC 2.81 & Jet 5. Is it possible to use the SoundEx function in a
query that's executed over ADO?

Thanks,
Ivan
Click to expand...
Click to expand...
 
Ivan,

Soundex is a Class that I have altered to a public function you can use in
your query. Add the following code into a module and declare it in a query as
per my first reply.

If you use this in the debug window you get the same Soundex code for a
different surname. I hope this helps.

?MakeSoundexA("MacFarlane")
M2164
?MakeSoundexA("McFarlane")
M2164




Option Compare Database
Option Explicit

Public Function MakeSoundexA(From As String) As String
Dim Codes(25) As Byte
Dim PrevCode As Byte
Dim CurrentCode As Byte
Dim i As Integer
Dim j As Integer
Dim CurrentChar As Integer
Dim Soundx As String
Dim A As Byte
Dim Z As Byte
Codes(0) = 0
Codes(1) = 1
Codes(2) = 2
Codes(3) = 3
Codes(4) = 0
Codes(5) = 1
Codes(6) = 2
Codes(7) = 0
Codes(8) = 0
Codes(9) = 2
Codes(10) = 2
Codes(11) = 4
Codes(12) = 5
Codes(13) = 5
Codes(14) = 0
Codes(15) = 1
Codes(16) = 2
Codes(17) = 6
Codes(18) = 2
Codes(19) = 3
Codes(20) = 0
Codes(21) = 1
Codes(22) = 0
Codes(23) = 2
Codes(24) = 0
Codes(25) = 2
If (Len(From) = 0) Then
MakeSoundexA = ""
Exit Function
End If
Z = Asc("Z")
A = Asc("A")
Soundx = UCase$(Left$(From, 1))
PrevCode = Codes(Asc(Left$(Soundx, 1)) - Asc("A"))
i = 2
Do While (i <= Len(From)) And Len(Soundx) < 5
CurrentChar = Asc(UCase$(Mid$(From, i, 1)))
If (CurrentChar >= A And CurrentChar <= Z) Then
CurrentCode = Codes(CurrentChar - A)
If (CurrentCode <> 0) Then
If (CurrentCode <> PrevCode) Then
Soundx = Soundx & Format$(CurrentCode)
End If
End If
PrevCode = CurrentCode
End If
i = i + 1
Loop
Soundx = Left$(Soundx & "00000", 5)
MakeSoundexA = Soundx
End Function
 
Thanks.

Alastair MacFarlane said:
Ivan,

Soundex is a Class that I have altered to a public function you can use in
your query. Add the following code into a module and declare it in a query as
per my first reply.

If you use this in the debug window you get the same Soundex code for a
different surname. I hope this helps.

?MakeSoundexA("MacFarlane")
M2164
?MakeSoundexA("McFarlane")
M2164




Option Compare Database
Option Explicit

Public Function MakeSoundexA(From As String) As String
Dim Codes(25) As Byte
Dim PrevCode As Byte
Dim CurrentCode As Byte
Dim i As Integer
Dim j As Integer
Dim CurrentChar As Integer
Dim Soundx As String
Dim A As Byte
Dim Z As Byte
Codes(0) = 0
Codes(1) = 1
Codes(2) = 2
Codes(3) = 3
Codes(4) = 0
Codes(5) = 1
Codes(6) = 2
Codes(7) = 0
Codes(8) = 0
Codes(9) = 2
Codes(10) = 2
Codes(11) = 4
Codes(12) = 5
Codes(13) = 5
Codes(14) = 0
Codes(15) = 1
Codes(16) = 2
Codes(17) = 6
Codes(18) = 2
Codes(19) = 3
Codes(20) = 0
Codes(21) = 1
Codes(22) = 0
Codes(23) = 2
Codes(24) = 0
Codes(25) = 2
If (Len(From) = 0) Then
MakeSoundexA = ""
Exit Function
End If
Z = Asc("Z")
A = Asc("A")
Soundx = UCase$(Left$(From, 1))
PrevCode = Codes(Asc(Left$(Soundx, 1)) - Asc("A"))
i = 2
Do While (i <= Len(From)) And Len(Soundx) < 5
CurrentChar = Asc(UCase$(Mid$(From, i, 1)))
If (CurrentChar >= A And CurrentChar <= Z) Then
CurrentCode = Codes(CurrentChar - A)
If (CurrentCode <> 0) Then
If (CurrentCode <> PrevCode) Then
Soundx = Soundx & Format$(CurrentCode)
End If
End If
PrevCode = CurrentCode
End If
i = i + 1
Loop
Soundx = Left$(Soundx & "00000", 5)
MakeSoundexA = Soundx
End Function
Click to expand...
 
Ivan said:
I guess it's from Access 2003.
Click to expand...

I think the latest version Jet 4.0 Service Pack 8.

If the Soundex functions isn't part of Jet, I don't think you can use
it via ADO.
 
Alastair said:
Ivan,

Soundex is a Class that I have altered to a public function you can
use in your query. Add the following code into a module and declare
it in a query as per my first reply.

If you use this in the debug window you get the same Soundex code for
a different surname. I hope this helps.

?MakeSoundexA("MacFarlane")
M2164
Click to expand...

Nicely done.
it's one of the shortest MS Basic ones I've seen and only about 50 lines
more than what can be done in Pick Basic.
(In Pick Basic all the conversion to numbers is done with one statement.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error 3601-Please help 7
Soundex 2
Soundex Function 2
Datatype mismatch in criteria expression 3
Soundex 16
Using User defined functions in query WHERE clauses 4
Soundex 1
Need for Speed 9

Back
Top