Strip leading characters from field name

[alhotch]

I am running Access 2007 and have a field name (RidersName) in table
(ReservationDetails). RidersName is a LastName, FirstName field with the
comma separating names. Records in this field could have leading blanks,
spaces, asterisks, and "wavy lines" (Spanish "enyeas" (sp?). These records
could have more than one of these "special" characters at the beginning of
the record and a combination of all. I am not a VB or SQL programmer (only
Fortran). How can I "strip" these unwanted characters from the beginning of
the records so as to get to the A thru Z characters only in LastName? I do
not want to change the content of the original records. I wish process this
"parsing" through a Query so as to pass the results to a ComboBox. Any ideas
out there?

 

[alhotch]

I am not sure where to put this "Trim" code. I built a query that lists
(sorted ascending) all RidersName records (264,278). I then added your "Trim"
code to the "Criteria" field and the query yields zero records. Here's what
an example of what the records look like before I apply the "stripping"
manipulation:

**Allen, Sam
***Allen, John
~~*Allen, Bob
<space>****Allen, Evan
<space><space>~*Allen, Greg
etc.

And here's what the results should be:

Allen, Sam
Allen, John
Allen, Bob
Allen, Evan
Allen, Greg
etc.

I want to keep the comma separating LastName/FirstName. Just need to strip
of the caharacters before the first valid characters (A thru Z)

 

[John Spencer]

I might use a custom function like this to find the position of the first
alpha character and then use Mid to get the value

Public Function fFindFirstPosition(strIN) As Long
Dim I As Long

If Len(strIN & "") = 0 Then
fFindFirstPosition = 0
Else
For I = 1 To Len(strIN)
If Mid(strIN, I, 1) Like "[A-Z]" Then
fFindFirstPosition = I
Exit For
End If
Next I

End If

End Function

In a query the call would look like

Mid(RidersName,fFindFirstPosition([RidersName])

Or you could modify the function to return the string in the first place.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[alhotch]

Thanks, John. As I stated earlier, I am not an SQL programmer. I need to know
where to place this code. Is it in the query ? I placed the query call in the
"Criteria" section and when executed, it give me the following error:

"Undefined function fFindFirstPosition in expression."

Obviuosly, I need to have the results of the SQL code passed to this process
since the query doesn't know about the "fFindFirstPosition" value.

I might use a custom function like this to find the position of the first
alpha character and then use Mid to get the value

Public Function fFindFirstPosition(strIN) As Long
Dim I As Long

If Len(strIN & "") = 0 Then
fFindFirstPosition = 0
Else
For I = 1 To Len(strIN)
If Mid(strIN, I, 1) Like "[A-Z]" Then
fFindFirstPosition = I
Exit For
End If
Next I

End If

End Function

In a query the call would look like

Mid(RidersName,fFindFirstPosition([RidersName])

Or you could modify the function to return the string in the first place.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am not sure where to put this "Trim" code. I built a query that lists
(sorted ascending) all RidersName records (264,278). I then added your "Trim"
code to the "Criteria" field and the query yields zero records. Here's what
an example of what the records look like before I apply the "stripping"
manipulation:

**Allen, Sam
***Allen, John
~~*Allen, Bob
<space>****Allen, Evan
<space><space>~*Allen, Greg
etc.

And here's what the results should be:

Allen, Sam
Allen, John
Allen, Bob
Allen, Evan
Allen, Greg
etc.

I want to keep the comma separating LastName/FirstName. Just need to strip
of the caharacters before the first valid characters (A thru Z)

 

[John Spencer]

You copy the function into a VBA module in the Access database and then save
the vba module with a name other than the name of the function - perhaps you
save the module as mod_myFunctions

In a query you then call the function in a calculated column.

Field: MyFixedName: Mid([YourFunkyFIeld],fFindFirstPosition([YourFunkyField])

This can be a bit slow since the function will get called for every record
that is returned.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks, John. As I stated earlier, I am not an SQL programmer. I need to know
where to place this code. Is it in the query ? I placed the query call in the
"Criteria" section and when executed, it give me the following error:

"Undefined function fFindFirstPosition in expression."

Obviuosly, I need to have the results of the SQL code passed to this process
since the query doesn't know about the "fFindFirstPosition" value.

I might use a custom function like this to find the position of the first
alpha character and then use Mid to get the value

Public Function fFindFirstPosition(strIN) As Long
Dim I As Long

If Len(strIN & "") = 0 Then
fFindFirstPosition = 0
Else
For I = 1 To Len(strIN)
If Mid(strIN, I, 1) Like "[A-Z]" Then
fFindFirstPosition = I
Exit For
End If
Next I

End If

End Function

In a query the call would look like

Mid(RidersName,fFindFirstPosition([RidersName])

Or you could modify the function to return the string in the first place.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am not sure where to put this "Trim" code. I built a query that lists
(sorted ascending) all RidersName records (264,278). I then added your "Trim"
code to the "Criteria" field and the query yields zero records. Here's what
an example of what the records look like before I apply the "stripping"
manipulation:

**Allen, Sam
***Allen, John
~~*Allen, Bob
<space>****Allen, Evan
<space><space>~*Allen, Greg
etc.

And here's what the results should be:

Allen, Sam
Allen, John
Allen, Bob
Allen, Evan
Allen, Greg
etc.

I want to keep the comma separating LastName/FirstName. Just need to strip
of the caharacters before the first valid characters (A thru Z)

:

alhotch wrote:
I am running Access 2007 and have a field name (RidersName) in table
(ReservationDetails). RidersName is a LastName, FirstName field with the
comma separating names. Records in this field could have leading blanks,
spaces, asterisks, and "wavy lines" (Spanish "enyeas" (sp?). These records
could have more than one of these "special" characters at the beginning of
the record and a combination of all. I am not a VB or SQL programmer (only
Fortran). How can I "strip" these unwanted characters from the beginning of
the records so as to get to the A thru Z characters only in LastName? I do
not want to change the content of the original records. I wish process this
"parsing" through a Query so as to pass the results to a ComboBox. Any ideas
out there?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

Trim(Replace(Replace(RidersName,"*",""),"~",""))

The Trim() removes leading and trailing spaces.
The Replace()s remove ANY asterisks and any "wavy" lines (~) from the
field: this means any of those characters - leading or trailing.

Did you say you wanted only the Last Name? The following may get that
(all on one line):

Left(Trim(Replace(Replace(RidersName,"*",""),"~","")),
InStr(Trim(Replace(Replace(RidersName,"*",""),"~","")), ",")-1)

It will miss any names that have only the last name (those without a
comma separating the last name from the first name). It will error out
w/ an "Invalid procedure call or argument" error. It would be a good
idea to have a criteria for the query that doesn't read first name-less
records.

WHERE RidersName LIKE "*,*"

The above says "Give me records that have a comma between strings of
characters." Something like this: "Saunders, Fred".

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSluRoYechKqOuFEgEQK1xgCgutnYW1p/qCVMT4ELPAtj9MFce5IAnjEi
YnF8VYBzsV6lQThPQnORkTsE
=EW0S
-----END PGP SIGNATURE-----

 

[alhotch]

Between John and "MG", I think I got it.
I created the VB Module named ParseFindRider; used the following function:
Mid(RidersName,fFindFirstPosition([RidersName])); and then pasted this
function into the "Field" cell of the fourth column of the query's design
grid (didn't know I could do that) and here's a sample snapshot of the output:

ReservationID RidersName PickUpDate Expr1
268382 ***~~Johnson, Andrea 8/2/2007 Johnson, Andrea
240785 ***~~Mackenzie, Nyla 8/31/2006 Mackenzie, Nyla
347613 ***~~Ryan, Jennifer 3/11/2009 Ryan, Jennifer
269210 ***~Heideman,Paul 5/9/2007 Heideman,Paul
347579 ***~Rowell, Jennifer 3/24/2009 Rowell, Jennifer
347579 ***~Rowell, Karen 3/24/2009 Rowell, Karen
252029 ***2 1/2, Boxes 12/6/2006 Boxes
353652 ***2 BAGS, Schwartz 5/13/2009 BAGS, Schwartz
349161 ***3 Boxes, Kevin Brown 3/27/2009 Boxes, Kevin Brown
277838 ***AAIBI Investigations, Package7/18/2007 AAIBI Investigations,
Package
352750 ***Abbassi, Essam 5/10/2009 Abbassi, Essam

I apologize for the formatting of the output. The "field" name of "Expr1" is
in the fourth grid column of the query - where the Mid code was pasted.
Notice the VB code also stripped off numbers as well as other characters
other than the names using only the letters A thru Z.

Thanks to BOTH OF YOU I think I can get to where I need to be. I'm
processing over 265,000 records and this query takes less than 2 seconds to
produce results.

Thanks again !!!
Al

 

[alhotch]

Well, I am almost out of the woods. I have discovered that any record that
begins with ANY NON A thru Z character (like *; 1; 333; .; etc) FOLLOWED by
blanks (or spaces) for the remainder of the field will generate a #Error
value in the Expr1 field. This is the result field from the "ParseFindRider"
VB code. When I ask the query to sort (ascending) the result in field Expr1.
I receive an "Invalid Procedure Call" error message.

What needs to be "treaked" in the SQL code provided by John Spencer to
ensure that ONLY characters A thru Z are processed into field Expr1 ?

Between John and "MG", I think I got it.
I created the VB Module named ParseFindRider; used the following function:
Mid(RidersName,fFindFirstPosition([RidersName])); and then pasted this
function into the "Field" cell of the fourth column of the query's design
grid (didn't know I could do that) and here's a sample snapshot of the output:

ReservationID RidersName PickUpDate Expr1
268382 ***~~Johnson, Andrea 8/2/2007 Johnson, Andrea
240785 ***~~Mackenzie, Nyla 8/31/2006 Mackenzie, Nyla
347613 ***~~Ryan, Jennifer 3/11/2009 Ryan, Jennifer
269210 ***~Heideman,Paul 5/9/2007 Heideman,Paul
347579 ***~Rowell, Jennifer 3/24/2009 Rowell, Jennifer
347579 ***~Rowell, Karen 3/24/2009 Rowell, Karen
252029 ***2 1/2, Boxes 12/6/2006 Boxes
353652 ***2 BAGS, Schwartz 5/13/2009 BAGS, Schwartz
349161 ***3 Boxes, Kevin Brown 3/27/2009 Boxes, Kevin Brown
277838 ***AAIBI Investigations, Package7/18/2007 AAIBI Investigations,
Package
352750 ***Abbassi, Essam 5/10/2009 Abbassi, Essam

I apologize for the formatting of the output. The "field" name of "Expr1" is
in the fourth grid column of the query - where the Mid code was pasted.
Notice the VB code also stripped off numbers as well as other characters
other than the names using only the letters A thru Z.

Thanks to BOTH OF YOU I think I can get to where I need to be. I'm
processing over 265,000 records and this query takes less than 2 seconds to
produce results.

Thanks again !!!
Al

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You put that formula in the "Field:" field of the query's design grid,
like this:

Rider: Trim(Replace(Replace(RidersName,"*",""),"~",""))

This will create a column (field) in the query results named "Rider."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSlzkWIechKqOuFEgEQKYLQCcDVoD8keGWcleE3RygIdWFekkX5UAn3IN
ScFuNb/yhcRZ+rxsa/RalQXv
=JfML
-----END PGP SIGNATURE-----

 

[John Spencer]

It kind of depends on what you want to do if the ridersname is "* " or if
the ridersname is "**************" etc.

If you just want to return nothing in those cases where there is never a
letter in ridersName try the following replacement for fFindFirstPosition

Public Function fFindFirstPosition(strIN) As Long
Dim I As Long
Dim sPos As Long

sPos = Len(strIN & "") + 1

If Len(strIN & "") = 0 Then
'use default value of of spos
Else
For I = 1 To Len(strIN)
If Mid(strIN, I, 1) Like "[A-Z]" Then
sPos = I
Exit For
End If
Next I
End If
fFindFirstPosition = sPos

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Well, I am almost out of the woods. I have discovered that any record that
begins with ANY NON A thru Z character (like *; 1; 333; .; etc) FOLLOWED by
blanks (or spaces) for the remainder of the field will generate a #Error
value in the Expr1 field. This is the result field from the "ParseFindRider"
VB code. When I ask the query to sort (ascending) the result in field Expr1.
I receive an "Invalid Procedure Call" error message.

What needs to be "treaked" in the SQL code provided by John Spencer to
ensure that ONLY characters A thru Z are processed into field Expr1 ?

Between John and "MG", I think I got it.
I created the VB Module named ParseFindRider; used the following function:
Mid(RidersName,fFindFirstPosition([RidersName])); and then pasted this
function into the "Field" cell of the fourth column of the query's design
grid (didn't know I could do that) and here's a sample snapshot of the output:

ReservationID RidersName PickUpDate Expr1
268382 ***~~Johnson, Andrea 8/2/2007 Johnson, Andrea
240785 ***~~Mackenzie, Nyla 8/31/2006 Mackenzie, Nyla
347613 ***~~Ryan, Jennifer 3/11/2009 Ryan, Jennifer
269210 ***~Heideman,Paul 5/9/2007 Heideman,Paul
347579 ***~Rowell, Jennifer 3/24/2009 Rowell, Jennifer
347579 ***~Rowell, Karen 3/24/2009 Rowell, Karen
252029 ***2 1/2, Boxes 12/6/2006 Boxes
353652 ***2 BAGS, Schwartz 5/13/2009 BAGS, Schwartz
349161 ***3 Boxes, Kevin Brown 3/27/2009 Boxes, Kevin Brown
277838 ***AAIBI Investigations, Package7/18/2007 AAIBI Investigations,
Package
352750 ***Abbassi, Essam 5/10/2009 Abbassi, Essam

I apologize for the formatting of the output. The "field" name of "Expr1" is
in the fourth grid column of the query - where the Mid code was pasted.
Notice the VB code also stripped off numbers as well as other characters
other than the names using only the letters A thru Z.

Thanks to BOTH OF YOU I think I can get to where I need to be. I'm
processing over 265,000 records and this query takes less than 2 seconds to
produce results.

Thanks again !!!
Al

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You put that formula in the "Field:" field of the query's design grid,
like this:

Rider: Trim(Replace(Replace(RidersName,"*",""),"~",""))

This will create a column (field) in the query results named "Rider."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSlzkWIechKqOuFEgEQKYLQCcDVoD8keGWcleE3RygIdWFekkX5UAn3IN
ScFuNb/yhcRZ+rxsa/RalQXv
=JfML
-----END PGP SIGNATURE-----

alhotch wrote:
I am not sure where to put this "Trim" code. I built a query that lists
(sorted ascending) all RidersName records (264,278). I then added your "Trim"
code to the "Criteria" field and the query yields zero records. Here's what
an example of what the records look like before I apply the "stripping"
manipulation:

**Allen, Sam
***Allen, John
~~*Allen, Bob
<space>****Allen, Evan
<space><space>~*Allen, Greg
etc.

And here's what the results should be:

Allen, Sam
Allen, John
Allen, Bob
Allen, Evan
Allen, Greg
etc.

I want to keep the comma separating LastName/FirstName. Just need to strip
of the caharacters before the first valid characters (A thru Z)

:

alhotch wrote:
I am running Access 2007 and have a field name (RidersName) in table
(ReservationDetails). RidersName is a LastName, FirstName field with the
comma separating names. Records in this field could have leading blanks,
spaces, asterisks, and "wavy lines" (Spanish "enyeas" (sp?). These records
could have more than one of these "special" characters at the beginning of
the record and a combination of all. I am not a VB or SQL programmer (only
Fortran). How can I "strip" these unwanted characters from the beginning of
the records so as to get to the A thru Z characters only in LastName? I do
not want to change the content of the original records. I wish process this
"parsing" through a Query so as to pass the results to a ComboBox. Any ideas
out there?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

Trim(Replace(Replace(RidersName,"*",""),"~",""))

The Trim() removes leading and trailing spaces.
The Replace()s remove ANY asterisks and any "wavy" lines (~) from the
field: this means any of those characters - leading or trailing.

Did you say you wanted only the Last Name? The following may get that
(all on one line):

Left(Trim(Replace(Replace(RidersName,"*",""),"~","")),
InStr(Trim(Replace(Replace(RidersName,"*",""),"~","")), ",")-1)

It will miss any names that have only the last name (those without a
comma separating the last name from the first name). It will error out
w/ an "Invalid procedure call or argument" error. It would be a good
idea to have a criteria for the query that doesn't read first name-less
records.

WHERE RidersName LIKE "*,*"

The above says "Give me records that have a comma between strings of
characters." Something like this: "Saunders, Fred".

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSluRoYechKqOuFEgEQK1xgCgutnYW1p/qCVMT4ELPAtj9MFce5IAnjEi
YnF8VYBzsV6lQThPQnORkTsE
=EW0S
-----END PGP SIGNATURE-----

 

[alhotch]

Thanks, John. If the RidersName field contains ANY leading characters OTHER
than a valid A thru Z character, strip them out. Where I get into trouble is
where I have these undesireable leading characters - followed by NO more
characters - undesireable or valid A thru Z. It's the blank characters for
the remainder of the field AFTER the undesireables that cause the error
message. Bottom line, if the field does not have any A thru Z characters
AFTER leading non A thru Z characters, out they go. I'll be trying your
latest code suggestion right away.

It kind of depends on what you want to do if the ridersname is "* " or if
the ridersname is "**************" etc.

If you just want to return nothing in those cases where there is never a
letter in ridersName try the following replacement for fFindFirstPosition

Public Function fFindFirstPosition(strIN) As Long
Dim I As Long
Dim sPos As Long

sPos = Len(strIN & "") + 1

If Len(strIN & "") = 0 Then
'use default value of of spos
Else
For I = 1 To Len(strIN)
If Mid(strIN, I, 1) Like "[A-Z]" Then
sPos = I
Exit For
End If
Next I
End If
fFindFirstPosition = sPos

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Well, I am almost out of the woods. I have discovered that any record that
begins with ANY NON A thru Z character (like *; 1; 333; .; etc) FOLLOWED by
blanks (or spaces) for the remainder of the field will generate a #Error
value in the Expr1 field. This is the result field from the "ParseFindRider"
VB code. When I ask the query to sort (ascending) the result in field Expr1.
I receive an "Invalid Procedure Call" error message.

What needs to be "treaked" in the SQL code provided by John Spencer to
ensure that ONLY characters A thru Z are processed into field Expr1 ?

Between John and "MG", I think I got it.
I created the VB Module named ParseFindRider; used the following function:
Mid(RidersName,fFindFirstPosition([RidersName])); and then pasted this
function into the "Field" cell of the fourth column of the query's design
grid (didn't know I could do that) and here's a sample snapshot of the output:

ReservationID RidersName PickUpDate Expr1
268382 ***~~Johnson, Andrea 8/2/2007 Johnson, Andrea
240785 ***~~Mackenzie, Nyla 8/31/2006 Mackenzie, Nyla
347613 ***~~Ryan, Jennifer 3/11/2009 Ryan, Jennifer
269210 ***~Heideman,Paul 5/9/2007 Heideman,Paul
347579 ***~Rowell, Jennifer 3/24/2009 Rowell, Jennifer
347579 ***~Rowell, Karen 3/24/2009 Rowell, Karen
252029 ***2 1/2, Boxes 12/6/2006 Boxes
353652 ***2 BAGS, Schwartz 5/13/2009 BAGS, Schwartz
349161 ***3 Boxes, Kevin Brown 3/27/2009 Boxes, Kevin Brown
277838 ***AAIBI Investigations, Package7/18/2007 AAIBI Investigations,
Package
352750 ***Abbassi, Essam 5/10/2009 Abbassi, Essam

I apologize for the formatting of the output. The "field" name of "Expr1" is
in the fourth grid column of the query - where the Mid code was pasted.
Notice the VB code also stripped off numbers as well as other characters
other than the names using only the letters A thru Z.

Thanks to BOTH OF YOU I think I can get to where I need to be. I'm
processing over 265,000 records and this query takes less than 2 seconds to
produce results.

Thanks again !!!
Al



:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You put that formula in the "Field:" field of the query's design grid,
like this:

Rider: Trim(Replace(Replace(RidersName,"*",""),"~",""))

This will create a column (field) in the query results named "Rider."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSlzkWIechKqOuFEgEQKYLQCcDVoD8keGWcleE3RygIdWFekkX5UAn3IN
ScFuNb/yhcRZ+rxsa/RalQXv
=JfML
-----END PGP SIGNATURE-----

alhotch wrote:
I am not sure where to put this "Trim" code. I built a query that lists
(sorted ascending) all RidersName records (264,278). I then added your "Trim"
code to the "Criteria" field and the query yields zero records. Here's what
an example of what the records look like before I apply the "stripping"
manipulation:

**Allen, Sam
***Allen, John
~~*Allen, Bob
<space>****Allen, Evan
<space><space>~*Allen, Greg
etc.

And here's what the results should be:

Allen, Sam
Allen, John
Allen, Bob
Allen, Evan
Allen, Greg
etc.

I want to keep the comma separating LastName/FirstName. Just need to strip
of the caharacters before the first valid characters (A thru Z)

:

alhotch wrote:
I am running Access 2007 and have a field name (RidersName) in table
(ReservationDetails). RidersName is a LastName, FirstName field with the
comma separating names. Records in this field could have leading blanks,
spaces, asterisks, and "wavy lines" (Spanish "enyeas" (sp?). These records
could have more than one of these "special" characters at the beginning of
the record and a combination of all. I am not a VB or SQL programmer (only
Fortran). How can I "strip" these unwanted characters from the beginning of
the records so as to get to the A thru Z characters only in LastName? I do
not want to change the content of the original records. I wish process this
"parsing" through a Query so as to pass the results to a ComboBox. Any ideas
out there?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

Trim(Replace(Replace(RidersName,"*",""),"~",""))

The Trim() removes leading and trailing spaces.
The Replace()s remove ANY asterisks and any "wavy" lines (~) from the
field: this means any of those characters - leading or trailing.

Did you say you wanted only the Last Name? The following may get that
(all on one line):

Left(Trim(Replace(Replace(RidersName,"*",""),"~","")),
InStr(Trim(Replace(Replace(RidersName,"*",""),"~","")), ",")-1)

It will miss any names that have only the last name (those without a
comma separating the last name from the first name). It will error out
w/ an "Invalid procedure call or argument" error. It would be a good
idea to have a criteria for the query that doesn't read first name-less
records.

WHERE RidersName LIKE "*,*"

The above says "Give me records that have a comma between strings of
characters." Something like this: "Saunders, Fred".

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSluRoYechKqOuFEgEQK1xgCgutnYW1p/qCVMT4ELPAtj9MFce5IAnjEi
YnF8VYBzsV6lQThPQnORkTsE
=EW0S
-----END PGP SIGNATURE-----

 

[alhotch]

John, new code seems to be working "as advertised". I am processing over
265,000 records and it takes almost a minute to cycle thru all records, just
as you said it would. Now, to figure out how to present the data in a fairly
responsive way. You know data entry people. Always want things to run very
fast.

Thanks again for your help and patience.
Al

Thanks, John. If the RidersName field contains ANY leading characters OTHER
than a valid A thru Z character, strip them out. Where I get into trouble is
where I have these undesireable leading characters - followed by NO more
characters - undesireable or valid A thru Z. It's the blank characters for
the remainder of the field AFTER the undesireables that cause the error
message. Bottom line, if the field does not have any A thru Z characters
AFTER leading non A thru Z characters, out they go. I'll be trying your
latest code suggestion right away.

It kind of depends on what you want to do if the ridersname is "* " or if
the ridersname is "**************" etc.

If you just want to return nothing in those cases where there is never a
letter in ridersName try the following replacement for fFindFirstPosition

Public Function fFindFirstPosition(strIN) As Long
Dim I As Long
Dim sPos As Long

sPos = Len(strIN & "") + 1

If Len(strIN & "") = 0 Then
'use default value of of spos
Else
For I = 1 To Len(strIN)
If Mid(strIN, I, 1) Like "[A-Z]" Then
sPos = I
Exit For
End If
Next I
End If
fFindFirstPosition = sPos

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Well, I am almost out of the woods. I have discovered that any record that
begins with ANY NON A thru Z character (like *; 1; 333; .; etc) FOLLOWED by
blanks (or spaces) for the remainder of the field will generate a #Error
value in the Expr1 field. This is the result field from the "ParseFindRider"
VB code. When I ask the query to sort (ascending) the result in field Expr1.
I receive an "Invalid Procedure Call" error message.

What needs to be "treaked" in the SQL code provided by John Spencer to
ensure that ONLY characters A thru Z are processed into field Expr1 ?

:

Between John and "MG", I think I got it.
I created the VB Module named ParseFindRider; used the following function:
Mid(RidersName,fFindFirstPosition([RidersName])); and then pasted this
function into the "Field" cell of the fourth column of the query's design
grid (didn't know I could do that) and here's a sample snapshot of the output:

ReservationID RidersName PickUpDate Expr1
268382 ***~~Johnson, Andrea 8/2/2007 Johnson, Andrea
240785 ***~~Mackenzie, Nyla 8/31/2006 Mackenzie, Nyla
347613 ***~~Ryan, Jennifer 3/11/2009 Ryan, Jennifer
269210 ***~Heideman,Paul 5/9/2007 Heideman,Paul
347579 ***~Rowell, Jennifer 3/24/2009 Rowell, Jennifer
347579 ***~Rowell, Karen 3/24/2009 Rowell, Karen
252029 ***2 1/2, Boxes 12/6/2006 Boxes
353652 ***2 BAGS, Schwartz 5/13/2009 BAGS, Schwartz
349161 ***3 Boxes, Kevin Brown 3/27/2009 Boxes, Kevin Brown
277838 ***AAIBI Investigations, Package7/18/2007 AAIBI Investigations,
Package
352750 ***Abbassi, Essam 5/10/2009 Abbassi, Essam

I apologize for the formatting of the output. The "field" name of "Expr1" is
in the fourth grid column of the query - where the Mid code was pasted.
Notice the VB code also stripped off numbers as well as other characters
other than the names using only the letters A thru Z.

Thanks to BOTH OF YOU I think I can get to where I need to be. I'm
processing over 265,000 records and this query takes less than 2 seconds to
produce results.

Thanks again !!!
Al



:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You put that formula in the "Field:" field of the query's design grid,
like this:

Rider: Trim(Replace(Replace(RidersName,"*",""),"~",""))

This will create a column (field) in the query results named "Rider."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSlzkWIechKqOuFEgEQKYLQCcDVoD8keGWcleE3RygIdWFekkX5UAn3IN
ScFuNb/yhcRZ+rxsa/RalQXv
=JfML
-----END PGP SIGNATURE-----

alhotch wrote:
I am not sure where to put this "Trim" code. I built a query that lists
(sorted ascending) all RidersName records (264,278). I then added your "Trim"
code to the "Criteria" field and the query yields zero records. Here's what
an example of what the records look like before I apply the "stripping"
manipulation:

**Allen, Sam
***Allen, John
~~*Allen, Bob
<space>****Allen, Evan
<space><space>~*Allen, Greg
etc.

And here's what the results should be:

Allen, Sam
Allen, John
Allen, Bob
Allen, Evan
Allen, Greg
etc.

I want to keep the comma separating LastName/FirstName. Just need to strip
of the caharacters before the first valid characters (A thru Z)

:

alhotch wrote:
I am running Access 2007 and have a field name (RidersName) in table
(ReservationDetails). RidersName is a LastName, FirstName field with the
comma separating names. Records in this field could have leading blanks,
spaces, asterisks, and "wavy lines" (Spanish "enyeas" (sp?). These records
could have more than one of these "special" characters at the beginning of
the record and a combination of all. I am not a VB or SQL programmer (only
Fortran). How can I "strip" these unwanted characters from the beginning of
the records so as to get to the A thru Z characters only in LastName? I do
not want to change the content of the original records. I wish process this
"parsing" through a Query so as to pass the results to a ComboBox. Any ideas
out there?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

Trim(Replace(Replace(RidersName,"*",""),"~",""))

The Trim() removes leading and trailing spaces.
The Replace()s remove ANY asterisks and any "wavy" lines (~) from the
field: this means any of those characters - leading or trailing.

Did you say you wanted only the Last Name? The following may get that
(all on one line):

Left(Trim(Replace(Replace(RidersName,"*",""),"~","")),
InStr(Trim(Replace(Replace(RidersName,"*",""),"~","")), ",")-1)

It will miss any names that have only the last name (those without a
comma separating the last name from the first name). It will error out
w/ an "Invalid procedure call or argument" error. It would be a good
idea to have a criteria for the query that doesn't read first name-less
records.

WHERE RidersName LIKE "*,*"

The above says "Give me records that have a comma between strings of
characters." Something like this: "Saunders, Fred".

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSluRoYechKqOuFEgEQK1xgCgutnYW1p/qCVMT4ELPAtj9MFce5IAnjEi
YnF8VYBzsV6lQThPQnORkTsE
=EW0S
-----END PGP SIGNATURE-----

 

[John Spencer]

Well, if you are displaying records then there is no reason to show
anyone 265,000 records at once. They should be filtered down to just a
few (or even one) if possible before you attempt to display them.

It might be worthwhile to add a new field (fldCleanName) to your table
and do an update on the table. Allow zero length values in this field.
If you plan to sort or filter on this field add an index to it.

UPDATE ReservationDetails
SET fldCleanName = Mid([RidersName],fFindFirstPosition([RidersName])

You might change the UPDATE to add a where clause

WHERE RidersName NOT LIKE "*" &
IIF(Len(fldCleanName & "")>0,fldCleanName,"JabberWocky") & "*"

And run the update periodically to catch changes and fix new records.

You will pay a time penalty while it scans 265,000 records for the
records to update, but even that could be fairly fast depending on your
network.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John, new code seems to be working "as advertised". I am processing over
265,000 records and it takes almost a minute to cycle thru all records, just
as you said it would. Now, to figure out how to present the data in a fairly
responsive way. You know data entry people. Always want things to run very
fast.

Thanks again for your help and patience.
Al

Thanks, John. If the RidersName field contains ANY leading characters OTHER
than a valid A thru Z character, strip them out. Where I get into trouble is
where I have these undesireable leading characters - followed by NO more
characters - undesireable or valid A thru Z. It's the blank characters for
the remainder of the field AFTER the undesireables that cause the error
message. Bottom line, if the field does not have any A thru Z characters
AFTER leading non A thru Z characters, out they go. I'll be trying your
latest code suggestion right away.

It kind of depends on what you want to do if the ridersname is "* " or if
the ridersname is "**************" etc.

If you just want to return nothing in those cases where there is never a
letter in ridersName try the following replacement for fFindFirstPosition

Public Function fFindFirstPosition(strIN) As Long
Dim I As Long
Dim sPos As Long

sPos = Len(strIN & "") + 1

If Len(strIN & "") = 0 Then
'use default value of of spos
Else
For I = 1 To Len(strIN)
If Mid(strIN, I, 1) Like "[A-Z]" Then
sPos = I
Exit For
End If
Next I
End If
fFindFirstPosition = sPos

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

alhotch wrote:
Well, I am almost out of the woods. I have discovered that any record that
begins with ANY NON A thru Z character (like *; 1; 333; .; etc) FOLLOWED by
blanks (or spaces) for the remainder of the field will generate a #Error
value in the Expr1 field. This is the result field from the "ParseFindRider"
VB code. When I ask the query to sort (ascending) the result in field Expr1.
I receive an "Invalid Procedure Call" error message.

What needs to be "treaked" in the SQL code provided by John Spencer to
ensure that ONLY characters A thru Z are processed into field Expr1 ?

:

Between John and "MG", I think I got it.
I created the VB Module named ParseFindRider; used the following function:
Mid(RidersName,fFindFirstPosition([RidersName])); and then pasted this
function into the "Field" cell of the fourth column of the query's design
grid (didn't know I could do that) and here's a sample snapshot of the output:

ReservationID RidersName PickUpDate Expr1
268382 ***~~Johnson, Andrea 8/2/2007 Johnson, Andrea
240785 ***~~Mackenzie, Nyla 8/31/2006 Mackenzie, Nyla
347613 ***~~Ryan, Jennifer 3/11/2009 Ryan, Jennifer
269210 ***~Heideman,Paul 5/9/2007 Heideman,Paul
347579 ***~Rowell, Jennifer 3/24/2009 Rowell, Jennifer
347579 ***~Rowell, Karen 3/24/2009 Rowell, Karen
252029 ***2 1/2, Boxes 12/6/2006 Boxes
353652 ***2 BAGS, Schwartz 5/13/2009 BAGS, Schwartz
349161 ***3 Boxes, Kevin Brown 3/27/2009 Boxes, Kevin Brown
277838 ***AAIBI Investigations, Package7/18/2007 AAIBI Investigations,
Package
352750 ***Abbassi, Essam 5/10/2009 Abbassi, Essam

I apologize for the formatting of the output. The "field" name of "Expr1" is
in the fourth grid column of the query - where the Mid code was pasted.
Notice the VB code also stripped off numbers as well as other characters
other than the names using only the letters A thru Z.

Thanks to BOTH OF YOU I think I can get to where I need to be. I'm
processing over 265,000 records and this query takes less than 2 seconds to
produce results.

Thanks again !!!
Al



:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You put that formula in the "Field:" field of the query's design grid,
like this:

Rider: Trim(Replace(Replace(RidersName,"*",""),"~",""))

This will create a column (field) in the query results named "Rider."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSlzkWIechKqOuFEgEQKYLQCcDVoD8keGWcleE3RygIdWFekkX5UAn3IN
ScFuNb/yhcRZ+rxsa/RalQXv
=JfML
-----END PGP SIGNATURE-----

alhotch wrote:
I am not sure where to put this "Trim" code. I built a query that lists
(sorted ascending) all RidersName records (264,278). I then added your "Trim"
code to the "Criteria" field and the query yields zero records. Here's what
an example of what the records look like before I apply the "stripping"
manipulation:

**Allen, Sam
***Allen, John
~~*Allen, Bob
<space>****Allen, Evan
<space><space>~*Allen, Greg
etc.

And here's what the results should be:

Allen, Sam
Allen, John
Allen, Bob
Allen, Evan
Allen, Greg
etc.

I want to keep the comma separating LastName/FirstName. Just need to strip
of the caharacters before the first valid characters (A thru Z)

:

alhotch wrote:
I am running Access 2007 and have a field name (RidersName) in table
(ReservationDetails). RidersName is a LastName, FirstName field with the
comma separating names. Records in this field could have leading blanks,
spaces, asterisks, and "wavy lines" (Spanish "enyeas" (sp?). These records
could have more than one of these "special" characters at the beginning of
the record and a combination of all. I am not a VB or SQL programmer (only
Fortran). How can I "strip" these unwanted characters from the beginning of
the records so as to get to the A thru Z characters only in LastName? I do
not want to change the content of the original records. I wish process this
"parsing" through a Query so as to pass the results to a ComboBox. Any ideas
out there?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

Trim(Replace(Replace(RidersName,"*",""),"~",""))

The Trim() removes leading and trailing spaces.
The Replace()s remove ANY asterisks and any "wavy" lines (~) from the
field: this means any of those characters - leading or trailing.

Did you say you wanted only the Last Name? The following may get that
(all on one line):

Left(Trim(Replace(Replace(RidersName,"*",""),"~","")),
InStr(Trim(Replace(Replace(RidersName,"*",""),"~","")), ",")-1)

It will miss any names that have only the last name (those without a
comma separating the last name from the first name). It will error out
w/ an "Invalid procedure call or argument" error. It would be a good
idea to have a criteria for the query that doesn't read first name-less
records.

WHERE RidersName LIKE "*,*"

The above says "Give me records that have a comma between strings of
characters." Something like this: "Saunders, Fred".

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSluRoYechKqOuFEgEQK1xgCgutnYW1p/qCVMT4ELPAtj9MFce5IAnjEi
YnF8VYBzsV6lQThPQnORkTsE
=EW0S
-----END PGP SIGNATURE-----

 

[alhotch]

Thanks, John. U R correct. Running the parse routine through a query call for
265,000 records (and this number will grow unless the DB is pruned) takes
approx. 20 seconds. Passing this process through a Combo Box takes about a
minute. Certainly this is not acceptable. I thought about adding a "parsed
FindRiders" table entry to the DB but as you said, frequent updates would
need to be executed to catch those changed and added records.

The way a reservationist would need to search for the RidersNames comes from
a customer inquiring about a day of travel and this day could have been 6
months or two years ago. I prune the DB annually to contain only the past
three years since some customers have "credit for future travel" on the
books. This search is by last name and without a "Soundex" (used by
California DMV) type name lookup program, exact spelling is required. Hence,
a list of last names, alphabetically listed, is currently what the
reservationists choose from to find these past travel records.

I could filter the search request by the first letter of the last name- "A"
would bring up ONLY a list of last names starting with the letter "A"; "B"
would bring up only the list of last names starting with the letter "B"; etc.
This would cut the search and parsing processing time significantly. Or, like
you suggested, create another field in the ReservationDetails table -
fldCleanName - and run a nightly update. This would give the reservationists
fast access to parsed records - for the entire database less the current date.

My environment has 10 reservation computers running the Front End DB on Dell
2400's - a little slow - running XP and Access 2003 (planning to upgrade to
Access 2007 Runtime) and the Back End DB is XP/Access 2003 on a fast Dell.
The network is gigabyte Cat6 ethernet.

I'll experiment with both ways and let you know.

Well, if you are displaying records then there is no reason to show
anyone 265,000 records at once. They should be filtered down to just a
few (or even one) if possible before you attempt to display them.

It might be worthwhile to add a new field (fldCleanName) to your table
and do an update on the table. Allow zero length values in this field.
If you plan to sort or filter on this field add an index to it.

UPDATE ReservationDetails
SET fldCleanName = Mid([RidersName],fFindFirstPosition([RidersName])

You might change the UPDATE to add a where clause

WHERE RidersName NOT LIKE "*" &
IIF(Len(fldCleanName & "")>0,fldCleanName,"JabberWocky") & "*"

And run the update periodically to catch changes and fix new records.

You will pay a time penalty while it scans 265,000 records for the
records to update, but even that could be fairly fast depending on your
network.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John, new code seems to be working "as advertised". I am processing over
265,000 records and it takes almost a minute to cycle thru all records, just
as you said it would. Now, to figure out how to present the data in a fairly
responsive way. You know data entry people. Always want things to run very
fast.

Thanks again for your help and patience.
Al

Thanks, John. If the RidersName field contains ANY leading characters OTHER
than a valid A thru Z character, strip them out. Where I get into trouble is
where I have these undesireable leading characters - followed by NO more
characters - undesireable or valid A thru Z. It's the blank characters for
the remainder of the field AFTER the undesireables that cause the error
message. Bottom line, if the field does not have any A thru Z characters
AFTER leading non A thru Z characters, out they go. I'll be trying your
latest code suggestion right away.

:

It kind of depends on what you want to do if the ridersname is "* " or if
the ridersname is "**************" etc.

If you just want to return nothing in those cases where there is never a
letter in ridersName try the following replacement for fFindFirstPosition

Public Function fFindFirstPosition(strIN) As Long
Dim I As Long
Dim sPos As Long

sPos = Len(strIN & "") + 1

If Len(strIN & "") = 0 Then
'use default value of of spos
Else
For I = 1 To Len(strIN)
If Mid(strIN, I, 1) Like "[A-Z]" Then
sPos = I
Exit For
End If
Next I
End If
fFindFirstPosition = sPos

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

alhotch wrote:
Well, I am almost out of the woods. I have discovered that any record that
begins with ANY NON A thru Z character (like *; 1; 333; .; etc) FOLLOWED by
blanks (or spaces) for the remainder of the field will generate a #Error
value in the Expr1 field. This is the result field from the "ParseFindRider"
VB code. When I ask the query to sort (ascending) the result in field Expr1.
I receive an "Invalid Procedure Call" error message.

What needs to be "treaked" in the SQL code provided by John Spencer to
ensure that ONLY characters A thru Z are processed into field Expr1 ?

:

Between John and "MG", I think I got it.
I created the VB Module named ParseFindRider; used the following function:
Mid(RidersName,fFindFirstPosition([RidersName])); and then pasted this
function into the "Field" cell of the fourth column of the query's design
grid (didn't know I could do that) and here's a sample snapshot of the output:

ReservationID RidersName PickUpDate Expr1
268382 ***~~Johnson, Andrea 8/2/2007 Johnson, Andrea
240785 ***~~Mackenzie, Nyla 8/31/2006 Mackenzie, Nyla
347613 ***~~Ryan, Jennifer 3/11/2009 Ryan, Jennifer
269210 ***~Heideman,Paul 5/9/2007 Heideman,Paul
347579 ***~Rowell, Jennifer 3/24/2009 Rowell, Jennifer
347579 ***~Rowell, Karen 3/24/2009 Rowell, Karen
252029 ***2 1/2, Boxes 12/6/2006 Boxes
353652 ***2 BAGS, Schwartz 5/13/2009 BAGS, Schwartz
349161 ***3 Boxes, Kevin Brown 3/27/2009 Boxes, Kevin Brown
277838 ***AAIBI Investigations, Package7/18/2007 AAIBI Investigations,
Package
352750 ***Abbassi, Essam 5/10/2009 Abbassi, Essam

I apologize for the formatting of the output. The "field" name of "Expr1" is
in the fourth grid column of the query - where the Mid code was pasted.
Notice the VB code also stripped off numbers as well as other characters
other than the names using only the letters A thru Z.

Thanks to BOTH OF YOU I think I can get to where I need to be. I'm
processing over 265,000 records and this query takes less than 2 seconds to
produce results.

Thanks again !!!
Al



:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You put that formula in the "Field:" field of the query's design grid,
like this:

Rider: Trim(Replace(Replace(RidersName,"*",""),"~",""))

This will create a column (field) in the query results named "Rider."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSlzkWIechKqOuFEgEQKYLQCcDVoD8keGWcleE3RygIdWFekkX5UAn3IN
ScFuNb/yhcRZ+rxsa/RalQXv
=JfML
-----END PGP SIGNATURE-----

alhotch wrote:
I am not sure where to put this "Trim" code. I built a query that lists
(sorted ascending) all RidersName records (264,278). I then added your "Trim"
code to the "Criteria" field and the query yields zero records. Here's what
an example of what the records look like before I apply the "stripping"
manipulation:

**Allen, Sam
***Allen, John
~~*Allen, Bob
<space>****Allen, Evan
<space><space>~*Allen, Greg
etc.

And here's what the results should be:

Allen, Sam
Allen, John
Allen, Bob
Allen, Evan
Allen, Greg
etc.

I want to keep the comma separating LastName/FirstName. Just need to strip
of the caharacters before the first valid characters (A thru Z)

:

alhotch wrote:
I am running Access 2007 and have a field name (RidersName) in table
(ReservationDetails). RidersName is a LastName, FirstName field with the
comma separating names. Records in this field could have leading blanks,
spaces, asterisks, and "wavy lines" (Spanish "enyeas" (sp?). These records
could have more than one of these "special" characters at the beginning of
the record and a combination of all. I am not a VB or SQL programmer (only
Fortran). How can I "strip" these unwanted characters from the beginning of
the records so as to get to the A thru Z characters only in LastName? I do
not want to change the content of the original records. I wish process this
"parsing" through a Query so as to pass the results to a ComboBox. Any ideas
out there?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

Trim(Replace(Replace(RidersName,"*",""),"~",""))

The Trim() removes leading and trailing spaces.
The Replace()s remove ANY asterisks and any "wavy" lines (~) from the
field: this means any of those characters - leading or trailing.

Did you say you wanted only the Last Name? The following may get that
(all on one line):

Left(Trim(Replace(Replace(RidersName,"*",""),"~","")),
InStr(Trim(Replace(Replace(RidersName,"*",""),"~","")), ",")-1)

It will miss any names that have only the last name (those without a
comma separating the last name from the first name). It will error out
w/ an "Invalid procedure call or argument" error. It would be a good
idea to have a criteria for the query that doesn't read first name-less
records.

WHERE RidersName LIKE "*,*"

The above says "Give me records that have a comma between strings of
characters." Something like this: "Saunders, Fred".

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSluRoYechKqOuFEgEQK1xgCgutnYW1p/qCVMT4ELPAtj9MFce5IAnjEi
YnF8VYBzsV6lQThPQnORkTsE
=EW0S
-----END PGP SIGNATURE-----

 

[John Spencer]

If you want to search by soundex, there are soundex routines. If you decide
to change your table structure you can also add a field fldNameSX (to hold the
calculated soundex value) which you could update in the same query.

Then you could show have the user input the last name into an unbound text box
and do a search for matches and return a very limited selection of possible
matches.

SELECT *
FROM ReservationDetails
WHERE fldCleanName Like [Forms]![FormName]![FindWhat] & "*"
OR fldNameSx = fSoundex([Forms]![FormName]![FindWhat])

If you have indexes on fldCleanName and fldNameSx you should get almost
instantaneous results for the matches.

Here is a very old Soundex routine that I have. I think that there are some
more efficient ones out there if you want to search the web for +VBA +Soundex
PS. Watch out for line wrapping in the newsreader breaking a line of code when
it should be one line.


Public Function fSoundex(strToEncode) As String
'AUTHOR: John Spencer
'LAST MODIFIED: June 30, 1999
'DESCRIPTION: Returns a string encoded as soundex code
'This version parallels the SOUNDEX used in MS SQL 6.5
'Procedure to encode string as soundex code using using the following rules
'Remove all w and h
'With exception of 1st character remove all aeiouy
'encode all letters in string
'collapse adjacent matching digits into one digit (3333 = 3)
'remove any zero values
'expand the code to 6 digits by adding zeroes to the end
'replace the first digit with the first letter of the original name
'KEEP first FOUR characters

Dim strSource As String, strEncode As String
Dim intPosition As Integer
Dim intLength As Integer
Dim strTEMP As String

On Error GoTo fSoundex_Error
'Get rid of leading & trailing spaces
strSource = Trim(strToEncode)

If Len(strSource) < 2 Then
strEncode = strSource & "000000"
Else
'Loop through remaining characters and encode them
For intPosition = 2 To Len(strSource)
Select Case Mid(strSource, intPosition, 1)
Case "b", "f", "p", "v" 'bfpv
strEncode = strEncode & "1"
Case "c", "g", "j", "k", "q", "s", "x", "z" 'cgjkqsxz
strEncode = strEncode & "2"
Case "d", "t" 'dt
strEncode = strEncode & "3"
Case "l" 'l
strEncode = strEncode & "4"
Case "m", "n" 'mn
strEncode = strEncode & "5"
Case "r" 'r
strEncode = strEncode & "6"
Case " " 'Space
strEncode = strEncode & "9"
Case Else
strEncode = strEncode & "0"
End Select
Next intPosition

If Len(strEncode) > 1 Then 'Remove adjacent duplicate codes
intLength = Len(strEncode)
For intPosition = intLength To 2 Step -1
If Mid(strEncode, intPosition - 1, 1) = _
Mid(strEncode, intPosition, 1) Then
strEncode = Mid(strEncode, 1, intPosition - 1) & _
Mid(strEncode, intPosition + 1)
End If
Next intPosition
End If


If Len(strEncode) > 1 Then 'REMOVE ZEROES
intLength = Len(strEncode)
For intPosition = 1 To intLength
If Mid(strEncode, intPosition, 1) <> "0" Then
strTEMP = strTEMP & Mid(strEncode, intPosition, 1)
End If
Next intPosition
strEncode = strTEMP
End If

strEncode = UCase(Left(strSource, 1)) & Mid(strEncode & "000000", 1, 5)

'if there is a space in the name then truncate at the space
If InStr(strEncode, "9") Then
strEncode = Left(strEncode, InStr(strEncode, "9") - 1) & "00000"
End If

End If 'Something is there

'Truncate value to 4 characters to conform with MS SQL 6.5 Soundex length
fSoundex = Mid(strEncode, 1, 4)
Exit Function

fSoundex_Error:
MsgBox Err.Description

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks, John. U R correct. Running the parse routine through a query call for
265,000 records (and this number will grow unless the DB is pruned) takes
approx. 20 seconds. Passing this process through a Combo Box takes about a
minute. Certainly this is not acceptable. I thought about adding a "parsed
FindRiders" table entry to the DB but as you said, frequent updates would
need to be executed to catch those changed and added records.

The way a reservationist would need to search for the RidersNames comes from
a customer inquiring about a day of travel and this day could have been 6
months or two years ago. I prune the DB annually to contain only the past
three years since some customers have "credit for future travel" on the
books. This search is by last name and without a "Soundex" (used by
California DMV) type name lookup program, exact spelling is required. Hence,
a list of last names, alphabetically listed, is currently what the
reservationists choose from to find these past travel records.

I could filter the search request by the first letter of the last name- "A"
would bring up ONLY a list of last names starting with the letter "A"; "B"
would bring up only the list of last names starting with the letter "B"; etc.
This would cut the search and parsing processing time significantly. Or, like
you suggested, create another field in the ReservationDetails table -
fldCleanName - and run a nightly update. This would give the reservationists
fast access to parsed records - for the entire database less the current date.

My environment has 10 reservation computers running the Front End DB on Dell
2400's - a little slow - running XP and Access 2003 (planning to upgrade to
Access 2007 Runtime) and the Back End DB is XP/Access 2003 on a fast Dell.
The network is gigabyte Cat6 ethernet.

I'll experiment with both ways and let you know.

Well, if you are displaying records then there is no reason to show
anyone 265,000 records at once. They should be filtered down to just a
few (or even one) if possible before you attempt to display them.

It might be worthwhile to add a new field (fldCleanName) to your table
and do an update on the table. Allow zero length values in this field.
If you plan to sort or filter on this field add an index to it.

UPDATE ReservationDetails
SET fldCleanName = Mid([RidersName],fFindFirstPosition([RidersName])

You might change the UPDATE to add a where clause

WHERE RidersName NOT LIKE "*" &
IIF(Len(fldCleanName & "")>0,fldCleanName,"JabberWocky") & "*"

And run the update periodically to catch changes and fix new records.

You will pay a time penalty while it scans 265,000 records for the
records to update, but even that could be fairly fast depending on your
network.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John, new code seems to be working "as advertised". I am processing over
265,000 records and it takes almost a minute to cycle thru all records, just
as you said it would. Now, to figure out how to present the data in a fairly
responsive way. You know data entry people. Always want things to run very
fast.

Thanks again for your help and patience.
Al

:

Thanks, John. If the RidersName field contains ANY leading characters OTHER
than a valid A thru Z character, strip them out. Where I get into trouble is
where I have these undesireable leading characters - followed by NO more
characters - undesireable or valid A thru Z. It's the blank characters for
the remainder of the field AFTER the undesireables that cause the error
message. Bottom line, if the field does not have any A thru Z characters
AFTER leading non A thru Z characters, out they go. I'll be trying your
latest code suggestion right away.

:

It kind of depends on what you want to do if the ridersname is "* " or if
the ridersname is "**************" etc.

If you just want to return nothing in those cases where there is never a
letter in ridersName try the following replacement for fFindFirstPosition

Public Function fFindFirstPosition(strIN) As Long
Dim I As Long
Dim sPos As Long

sPos = Len(strIN & "") + 1

If Len(strIN & "") = 0 Then
'use default value of of spos
Else
For I = 1 To Len(strIN)
If Mid(strIN, I, 1) Like "[A-Z]" Then
sPos = I
Exit For
End If
Next I
End If
fFindFirstPosition = sPos

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

alhotch wrote:
Well, I am almost out of the woods. I have discovered that any record that
begins with ANY NON A thru Z character (like *; 1; 333; .; etc) FOLLOWED by
blanks (or spaces) for the remainder of the field will generate a #Error
value in the Expr1 field. This is the result field from the "ParseFindRider"
VB code. When I ask the query to sort (ascending) the result in field Expr1.
I receive an "Invalid Procedure Call" error message.

What needs to be "treaked" in the SQL code provided by John Spencer to
ensure that ONLY characters A thru Z are processed into field Expr1 ?

:

Between John and "MG", I think I got it.
I created the VB Module named ParseFindRider; used the following function:
Mid(RidersName,fFindFirstPosition([RidersName])); and then pasted this
function into the "Field" cell of the fourth column of the query's design
grid (didn't know I could do that) and here's a sample snapshot of the output:

ReservationID RidersName PickUpDate Expr1
268382 ***~~Johnson, Andrea 8/2/2007 Johnson, Andrea
240785 ***~~Mackenzie, Nyla 8/31/2006 Mackenzie, Nyla
347613 ***~~Ryan, Jennifer 3/11/2009 Ryan, Jennifer
269210 ***~Heideman,Paul 5/9/2007 Heideman,Paul
347579 ***~Rowell, Jennifer 3/24/2009 Rowell, Jennifer
347579 ***~Rowell, Karen 3/24/2009 Rowell, Karen
252029 ***2 1/2, Boxes 12/6/2006 Boxes
353652 ***2 BAGS, Schwartz 5/13/2009 BAGS, Schwartz
349161 ***3 Boxes, Kevin Brown 3/27/2009 Boxes, Kevin Brown
277838 ***AAIBI Investigations, Package7/18/2007 AAIBI Investigations,
Package
352750 ***Abbassi, Essam 5/10/2009 Abbassi, Essam

I apologize for the formatting of the output. The "field" name of "Expr1" is
in the fourth grid column of the query - where the Mid code was pasted.
Notice the VB code also stripped off numbers as well as other characters
other than the names using only the letters A thru Z.

Thanks to BOTH OF YOU I think I can get to where I need to be. I'm
processing over 265,000 records and this query takes less than 2 seconds to
produce results.

Thanks again !!!
Al



:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You put that formula in the "Field:" field of the query's design grid,
like this:

Rider: Trim(Replace(Replace(RidersName,"*",""),"~",""))

This will create a column (field) in the query results named "Rider."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSlzkWIechKqOuFEgEQKYLQCcDVoD8keGWcleE3RygIdWFekkX5UAn3IN
ScFuNb/yhcRZ+rxsa/RalQXv
=JfML
-----END PGP SIGNATURE-----

alhotch wrote:
I am not sure where to put this "Trim" code. I built a query that lists
(sorted ascending) all RidersName records (264,278). I then added your "Trim"
code to the "Criteria" field and the query yields zero records. Here's what
an example of what the records look like before I apply the "stripping"
manipulation:

**Allen, Sam
***Allen, John
~~*Allen, Bob
<space>****Allen, Evan
<space><space>~*Allen, Greg
etc.

And here's what the results should be:

Allen, Sam
Allen, John
Allen, Bob
Allen, Evan
Allen, Greg
etc.

I want to keep the comma separating LastName/FirstName. Just need to strip
of the caharacters before the first valid characters (A thru Z)

:

alhotch wrote:
I am running Access 2007 and have a field name (RidersName) in table
(ReservationDetails). RidersName is a LastName, FirstName field with the
comma separating names. Records in this field could have leading blanks,
spaces, asterisks, and "wavy lines" (Spanish "enyeas" (sp?). These records
could have more than one of these "special" characters at the beginning of
the record and a combination of all. I am not a VB or SQL programmer (only
Fortran). How can I "strip" these unwanted characters from the beginning of
the records so as to get to the A thru Z characters only in LastName? I do
not want to change the content of the original records. I wish process this
"parsing" through a Query so as to pass the results to a ComboBox. Any ideas
out there?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

Trim(Replace(Replace(RidersName,"*",""),"~",""))

The Trim() removes leading and trailing spaces.
The Replace()s remove ANY asterisks and any "wavy" lines (~) from the
field: this means any of those characters - leading or trailing.

Did you say you wanted only the Last Name? The following may get that
(all on one line):

Left(Trim(Replace(Replace(RidersName,"*",""),"~","")),
InStr(Trim(Replace(Replace(RidersName,"*",""),"~","")), ",")-1)

It will miss any names that have only the last name (those without a
comma separating the last name from the first name). It will error out
w/ an "Invalid procedure call or argument" error. It would be a good
idea to have a criteria for the query that doesn't read first name-less
records.

WHERE RidersName LIKE "*,*"

The above says "Give me records that have a comma between strings of
characters." Something like this: "Saunders, Fred".

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSluRoYechKqOuFEgEQK1xgCgutnYW1p/qCVMT4ELPAtj9MFce5IAnjEi
YnF8VYBzsV6lQThPQnORkTsE
=EW0S
-----END PGP SIGNATURE-----

 

[alhotch]

WOW ! You have certainly given me some interesting ideas to work with. I'll
need a few day to absorb all this. Thanks , John !!!!! I may trade in my
Fortran experience (31 years with the IBM Corporation in various positions)
for SQL/VB yet !!!

If you want to search by soundex, there are soundex routines. If you decide
to change your table structure you can also add a field fldNameSX (to hold the
calculated soundex value) which you could update in the same query.

Then you could show have the user input the last name into an unbound text box
and do a search for matches and return a very limited selection of possible
matches.

SELECT *
FROM ReservationDetails
WHERE fldCleanName Like [Forms]![FormName]![FindWhat] & "*"
OR fldNameSx = fSoundex([Forms]![FormName]![FindWhat])

If you have indexes on fldCleanName and fldNameSx you should get almost
instantaneous results for the matches.

Here is a very old Soundex routine that I have. I think that there are some
more efficient ones out there if you want to search the web for +VBA +Soundex
PS. Watch out for line wrapping in the newsreader breaking a line of code when
it should be one line.


Public Function fSoundex(strToEncode) As String
'AUTHOR: John Spencer
'LAST MODIFIED: June 30, 1999
'DESCRIPTION: Returns a string encoded as soundex code
'This version parallels the SOUNDEX used in MS SQL 6.5
'Procedure to encode string as soundex code using using the following rules
'Remove all w and h
'With exception of 1st character remove all aeiouy
'encode all letters in string
'collapse adjacent matching digits into one digit (3333 = 3)
'remove any zero values
'expand the code to 6 digits by adding zeroes to the end
'replace the first digit with the first letter of the original name
'KEEP first FOUR characters

Dim strSource As String, strEncode As String
Dim intPosition As Integer
Dim intLength As Integer
Dim strTEMP As String

On Error GoTo fSoundex_Error
'Get rid of leading & trailing spaces
strSource = Trim(strToEncode)

If Len(strSource) < 2 Then
strEncode = strSource & "000000"
Else
'Loop through remaining characters and encode them
For intPosition = 2 To Len(strSource)
Select Case Mid(strSource, intPosition, 1)
Case "b", "f", "p", "v" 'bfpv
strEncode = strEncode & "1"
Case "c", "g", "j", "k", "q", "s", "x", "z" 'cgjkqsxz
strEncode = strEncode & "2"
Case "d", "t" 'dt
strEncode = strEncode & "3"
Case "l" 'l
strEncode = strEncode & "4"
Case "m", "n" 'mn
strEncode = strEncode & "5"
Case "r" 'r
strEncode = strEncode & "6"
Case " " 'Space
strEncode = strEncode & "9"
Case Else
strEncode = strEncode & "0"
End Select
Next intPosition

If Len(strEncode) > 1 Then 'Remove adjacent duplicate codes
intLength = Len(strEncode)
For intPosition = intLength To 2 Step -1
If Mid(strEncode, intPosition - 1, 1) = _
Mid(strEncode, intPosition, 1) Then
strEncode = Mid(strEncode, 1, intPosition - 1) & _
Mid(strEncode, intPosition + 1)
End If
Next intPosition
End If


If Len(strEncode) > 1 Then 'REMOVE ZEROES
intLength = Len(strEncode)
For intPosition = 1 To intLength
If Mid(strEncode, intPosition, 1) <> "0" Then
strTEMP = strTEMP & Mid(strEncode, intPosition, 1)
End If
Next intPosition
strEncode = strTEMP
End If

strEncode = UCase(Left(strSource, 1)) & Mid(strEncode & "000000", 1, 5)

'if there is a space in the name then truncate at the space
If InStr(strEncode, "9") Then
strEncode = Left(strEncode, InStr(strEncode, "9") - 1) & "00000"
End If

End If 'Something is there

'Truncate value to 4 characters to conform with MS SQL 6.5 Soundex length
fSoundex = Mid(strEncode, 1, 4)
Exit Function

fSoundex_Error:
MsgBox Err.Description

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks, John. U R correct. Running the parse routine through a query call for
265,000 records (and this number will grow unless the DB is pruned) takes
approx. 20 seconds. Passing this process through a Combo Box takes about a
minute. Certainly this is not acceptable. I thought about adding a "parsed
FindRiders" table entry to the DB but as you said, frequent updates would
need to be executed to catch those changed and added records.

The way a reservationist would need to search for the RidersNames comes from
a customer inquiring about a day of travel and this day could have been 6
months or two years ago. I prune the DB annually to contain only the past
three years since some customers have "credit for future travel" on the
books. This search is by last name and without a "Soundex" (used by
California DMV) type name lookup program, exact spelling is required. Hence,
a list of last names, alphabetically listed, is currently what the
reservationists choose from to find these past travel records.

I could filter the search request by the first letter of the last name- "A"
would bring up ONLY a list of last names starting with the letter "A"; "B"
would bring up only the list of last names starting with the letter "B"; etc.
This would cut the search and parsing processing time significantly. Or, like
you suggested, create another field in the ReservationDetails table -
fldCleanName - and run a nightly update. This would give the reservationists
fast access to parsed records - for the entire database less the current date.

My environment has 10 reservation computers running the Front End DB on Dell
2400's - a little slow - running XP and Access 2003 (planning to upgrade to
Access 2007 Runtime) and the Back End DB is XP/Access 2003 on a fast Dell.
The network is gigabyte Cat6 ethernet.

I'll experiment with both ways and let you know.

Well, if you are displaying records then there is no reason to show
anyone 265,000 records at once. They should be filtered down to just a
few (or even one) if possible before you attempt to display them.

It might be worthwhile to add a new field (fldCleanName) to your table
and do an update on the table. Allow zero length values in this field.
If you plan to sort or filter on this field add an index to it.

UPDATE ReservationDetails
SET fldCleanName = Mid([RidersName],fFindFirstPosition([RidersName])

You might change the UPDATE to add a where clause

WHERE RidersName NOT LIKE "*" &
IIF(Len(fldCleanName & "")>0,fldCleanName,"JabberWocky") & "*"

And run the update periodically to catch changes and fix new records.

You will pay a time penalty while it scans 265,000 records for the
records to update, but even that could be fairly fast depending on your
network.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


alhotch wrote:
John, new code seems to be working "as advertised". I am processing over
265,000 records and it takes almost a minute to cycle thru all records, just
as you said it would. Now, to figure out how to present the data in a fairly
responsive way. You know data entry people. Always want things to run very
fast.

Thanks again for your help and patience.
Al

:

Thanks, John. If the RidersName field contains ANY leading characters OTHER
than a valid A thru Z character, strip them out. Where I get into trouble is
where I have these undesireable leading characters - followed by NO more
characters - undesireable or valid A thru Z. It's the blank characters for
the remainder of the field AFTER the undesireables that cause the error
message. Bottom line, if the field does not have any A thru Z characters
AFTER leading non A thru Z characters, out they go. I'll be trying your
latest code suggestion right away.

:

It kind of depends on what you want to do if the ridersname is "* " or if
the ridersname is "**************" etc.

If you just want to return nothing in those cases where there is never a
letter in ridersName try the following replacement for fFindFirstPosition

Public Function fFindFirstPosition(strIN) As Long
Dim I As Long
Dim sPos As Long

sPos = Len(strIN & "") + 1

If Len(strIN & "") = 0 Then
'use default value of of spos
Else
For I = 1 To Len(strIN)
If Mid(strIN, I, 1) Like "[A-Z]" Then
sPos = I
Exit For
End If
Next I
End If
fFindFirstPosition = sPos

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

alhotch wrote:
Well, I am almost out of the woods. I have discovered that any record that
begins with ANY NON A thru Z character (like *; 1; 333; .; etc) FOLLOWED by
blanks (or spaces) for the remainder of the field will generate a #Error
value in the Expr1 field. This is the result field from the "ParseFindRider"
VB code. When I ask the query to sort (ascending) the result in field Expr1.
I receive an "Invalid Procedure Call" error message.

What needs to be "treaked" in the SQL code provided by John Spencer to
ensure that ONLY characters A thru Z are processed into field Expr1 ?

:

Between John and "MG", I think I got it.
I created the VB Module named ParseFindRider; used the following function:
Mid(RidersName,fFindFirstPosition([RidersName])); and then pasted this
function into the "Field" cell of the fourth column of the query's design
grid (didn't know I could do that) and here's a sample snapshot of the output:

ReservationID RidersName PickUpDate Expr1
268382 ***~~Johnson, Andrea 8/2/2007 Johnson, Andrea
240785 ***~~Mackenzie, Nyla 8/31/2006 Mackenzie, Nyla
347613 ***~~Ryan, Jennifer 3/11/2009 Ryan, Jennifer
269210 ***~Heideman,Paul 5/9/2007 Heideman,Paul
347579 ***~Rowell, Jennifer 3/24/2009 Rowell, Jennifer
347579 ***~Rowell, Karen 3/24/2009 Rowell, Karen
252029 ***2 1/2, Boxes 12/6/2006 Boxes
353652 ***2 BAGS, Schwartz 5/13/2009 BAGS, Schwartz
349161 ***3 Boxes, Kevin Brown 3/27/2009 Boxes, Kevin Brown
277838 ***AAIBI Investigations, Package7/18/2007 AAIBI Investigations,
Package
352750 ***Abbassi, Essam 5/10/2009 Abbassi, Essam

I apologize for the formatting of the output. The "field" name of "Expr1" is
in the fourth grid column of the query - where the Mid code was pasted.
Notice the VB code also stripped off numbers as well as other characters
other than the names using only the letters A thru Z.

Thanks to BOTH OF YOU I think I can get to where I need to be. I'm
processing over 265,000 records and this query takes less than 2 seconds to
produce results.

Thanks again !!!
Al



:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You put that formula in the "Field:" field of the query's design grid,
like this:

Rider: Trim(Replace(Replace(RidersName,"*",""),"~",""))

This will create a column (field) in the query results named "Rider."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----

 

[alhotch]

John just followup info.
I have used your "parse" routine quite successfully !!! Much thanks for all
your help !!! I run a "MakeTable" Query to initially create the
"FilteredRidersList" table. I then, overnight, run a "Delete" Query to clean
out all records and then run an "Append" Query to the same table to catch the
latest riders (filtered). I do this so my users will have the latest records
minus one day (the current date) since I am adding records everyday and I do
not want to run the "parse" routine each time a record (Rider) is added. I
also have modified the "CustomerPhoneList" from MS Northwind.mdb to present
the filtered records - optionally by the first letter of the LastName or
AlllRecords. When the "FilteredRiders" form is launched, it takes only 3-4
seconds to display all 265k+ filtered records - FAR better than using a
ComboBox which took over a minute to display the same number of records.

Again, John. THANK YOU !!!!

WOW ! You have certainly given me some interesting ideas to work with. I'll
need a few day to absorb all this. Thanks , John !!!!! I may trade in my
Fortran experience (31 years with the IBM Corporation in various positions)
for SQL/VB yet !!!

If you want to search by soundex, there are soundex routines. If you decide
to change your table structure you can also add a field fldNameSX (to hold the
calculated soundex value) which you could update in the same query.

Then you could show have the user input the last name into an unbound text box
and do a search for matches and return a very limited selection of possible
matches.

SELECT *
FROM ReservationDetails
WHERE fldCleanName Like [Forms]![FormName]![FindWhat] & "*"
OR fldNameSx = fSoundex([Forms]![FormName]![FindWhat])

If you have indexes on fldCleanName and fldNameSx you should get almost
instantaneous results for the matches.

Here is a very old Soundex routine that I have. I think that there are some
more efficient ones out there if you want to search the web for +VBA +Soundex
PS. Watch out for line wrapping in the newsreader breaking a line of code when
it should be one line.


Public Function fSoundex(strToEncode) As String
'AUTHOR: John Spencer
'LAST MODIFIED: June 30, 1999
'DESCRIPTION: Returns a string encoded as soundex code
'This version parallels the SOUNDEX used in MS SQL 6.5
'Procedure to encode string as soundex code using using the following rules
'Remove all w and h
'With exception of 1st character remove all aeiouy
'encode all letters in string
'collapse adjacent matching digits into one digit (3333 = 3)
'remove any zero values
'expand the code to 6 digits by adding zeroes to the end
'replace the first digit with the first letter of the original name
'KEEP first FOUR characters

Dim strSource As String, strEncode As String
Dim intPosition As Integer
Dim intLength As Integer
Dim strTEMP As String

On Error GoTo fSoundex_Error
'Get rid of leading & trailing spaces
strSource = Trim(strToEncode)

If Len(strSource) < 2 Then
strEncode = strSource & "000000"
Else
'Loop through remaining characters and encode them
For intPosition = 2 To Len(strSource)
Select Case Mid(strSource, intPosition, 1)
Case "b", "f", "p", "v" 'bfpv
strEncode = strEncode & "1"
Case "c", "g", "j", "k", "q", "s", "x", "z" 'cgjkqsxz
strEncode = strEncode & "2"
Case "d", "t" 'dt
strEncode = strEncode & "3"
Case "l" 'l
strEncode = strEncode & "4"
Case "m", "n" 'mn
strEncode = strEncode & "5"
Case "r" 'r
strEncode = strEncode & "6"
Case " " 'Space
strEncode = strEncode & "9"
Case Else
strEncode = strEncode & "0"
End Select
Next intPosition

If Len(strEncode) > 1 Then 'Remove adjacent duplicate codes
intLength = Len(strEncode)
For intPosition = intLength To 2 Step -1
If Mid(strEncode, intPosition - 1, 1) = _
Mid(strEncode, intPosition, 1) Then
strEncode = Mid(strEncode, 1, intPosition - 1) & _
Mid(strEncode, intPosition + 1)
End If
Next intPosition
End If


If Len(strEncode) > 1 Then 'REMOVE ZEROES
intLength = Len(strEncode)
For intPosition = 1 To intLength
If Mid(strEncode, intPosition, 1) <> "0" Then
strTEMP = strTEMP & Mid(strEncode, intPosition, 1)
End If
Next intPosition
strEncode = strTEMP
End If

strEncode = UCase(Left(strSource, 1)) & Mid(strEncode & "000000", 1, 5)

'if there is a space in the name then truncate at the space
If InStr(strEncode, "9") Then
strEncode = Left(strEncode, InStr(strEncode, "9") - 1) & "00000"
End If

End If 'Something is there

'Truncate value to 4 characters to conform with MS SQL 6.5 Soundex length
fSoundex = Mid(strEncode, 1, 4)
Exit Function

fSoundex_Error:
MsgBox Err.Description

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks, John. U R correct. Running the parse routine through a query call for
265,000 records (and this number will grow unless the DB is pruned) takes
approx. 20 seconds. Passing this process through a Combo Box takes about a
minute. Certainly this is not acceptable. I thought about adding a "parsed
FindRiders" table entry to the DB but as you said, frequent updates would
need to be executed to catch those changed and added records.

The way a reservationist would need to search for the RidersNames comes from
a customer inquiring about a day of travel and this day could have been 6
months or two years ago. I prune the DB annually to contain only the past
three years since some customers have "credit for future travel" on the
books. This search is by last name and without a "Soundex" (used by
California DMV) type name lookup program, exact spelling is required. Hence,
a list of last names, alphabetically listed, is currently what the
reservationists choose from to find these past travel records.

I could filter the search request by the first letter of the last name- "A"
would bring up ONLY a list of last names starting with the letter "A"; "B"
would bring up only the list of last names starting with the letter "B"; etc.
This would cut the search and parsing processing time significantly. Or, like
you suggested, create another field in the ReservationDetails table -
fldCleanName - and run a nightly update. This would give the reservationists
fast access to parsed records - for the entire database less the current date.

My environment has 10 reservation computers running the Front End DB on Dell
2400's - a little slow - running XP and Access 2003 (planning to upgrade to
Access 2007 Runtime) and the Back End DB is XP/Access 2003 on a fast Dell.
The network is gigabyte Cat6 ethernet.

I'll experiment with both ways and let you know.

:

Well, if you are displaying records then there is no reason to show
anyone 265,000 records at once. They should be filtered down to just a
few (or even one) if possible before you attempt to display them.

It might be worthwhile to add a new field (fldCleanName) to your table
and do an update on the table. Allow zero length values in this field.
If you plan to sort or filter on this field add an index to it.

UPDATE ReservationDetails
SET fldCleanName = Mid([RidersName],fFindFirstPosition([RidersName])

You might change the UPDATE to add a where clause

WHERE RidersName NOT LIKE "*" &
IIF(Len(fldCleanName & "")>0,fldCleanName,"JabberWocky") & "*"

And run the update periodically to catch changes and fix new records.

You will pay a time penalty while it scans 265,000 records for the
records to update, but even that could be fairly fast depending on your
network.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


alhotch wrote:
John, new code seems to be working "as advertised". I am processing over
265,000 records and it takes almost a minute to cycle thru all records, just
as you said it would. Now, to figure out how to present the data in a fairly
responsive way. You know data entry people. Always want things to run very
fast.

Thanks again for your help and patience.
Al

:

Thanks, John. If the RidersName field contains ANY leading characters OTHER
than a valid A thru Z character, strip them out. Where I get into trouble is
where I have these undesireable leading characters - followed by NO more
characters - undesireable or valid A thru Z. It's the blank characters for
the remainder of the field AFTER the undesireables that cause the error
message. Bottom line, if the field does not have any A thru Z characters
AFTER leading non A thru Z characters, out they go. I'll be trying your
latest code suggestion right away.

:

It kind of depends on what you want to do if the ridersname is "* " or if
the ridersname is "**************" etc.

If you just want to return nothing in those cases where there is never a
letter in ridersName try the following replacement for fFindFirstPosition

Public Function fFindFirstPosition(strIN) As Long
Dim I As Long
Dim sPos As Long

sPos = Len(strIN & "") + 1

If Len(strIN & "") = 0 Then
'use default value of of spos
Else
For I = 1 To Len(strIN)
If Mid(strIN, I, 1) Like "[A-Z]" Then
sPos = I
Exit For
End If
Next I
End If
fFindFirstPosition = sPos

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

alhotch wrote:
Well, I am almost out of the woods. I have discovered that any record that
begins with ANY NON A thru Z character (like *; 1; 333; .; etc) FOLLOWED by
blanks (or spaces) for the remainder of the field will generate a #Error
value in the Expr1 field. This is the result field from the "ParseFindRider"
VB code. When I ask the query to sort (ascending) the result in field Expr1.
I receive an "Invalid Procedure Call" error message.

What needs to be "treaked" in the SQL code provided by John Spencer to
ensure that ONLY characters A thru Z are processed into field Expr1 ?

:

Between John and "MG", I think I got it.
I created the VB Module named ParseFindRider; used the following function:
Mid(RidersName,fFindFirstPosition([RidersName])); and then pasted this
function into the "Field" cell of the fourth column of the query's design
grid (didn't know I could do that) and here's a sample snapshot of the output:

ReservationID RidersName PickUpDate Expr1
268382 ***~~Johnson, Andrea 8/2/2007 Johnson, Andrea
240785 ***~~Mackenzie, Nyla 8/31/2006 Mackenzie, Nyla
347613 ***~~Ryan, Jennifer 3/11/2009 Ryan, Jennifer
269210 ***~Heideman,Paul 5/9/2007 Heideman,Paul
347579 ***~Rowell, Jennifer 3/24/2009 Rowell, Jennifer
347579 ***~Rowell, Karen 3/24/2009 Rowell, Karen
252029 ***2 1/2, Boxes 12/6/2006 Boxes
353652 ***2 BAGS, Schwartz 5/13/2009 BAGS, Schwartz
349161 ***3 Boxes, Kevin Brown 3/27/2009 Boxes, Kevin Brown
277838 ***AAIBI Investigations, Package7/18/2007 AAIBI Investigations,
Package
352750 ***Abbassi, Essam 5/10/2009 Abbassi, Essam

I apologize for the formatting of the output. The "field" name of "Expr1" is
in the fourth grid column of the query - where the Mid code was pasted.
Notice the VB code also stripped off numbers as well as other characters
other than the names using only the letters A thru Z.

Thanks to BOTH OF YOU I think I can get to where I need to be. I'm
processing over 265,000 records and this query takes less than 2 seconds to
produce results.

Thanks again !!!
Al



:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You put that formula in the "Field:" field of the query's design grid,
like this:

Rider: Trim(Replace(Replace(RidersName,"*",""),"~",""))

This will create a column (field) in the query results named "Rider."