Ignoring certain characters in data

[DevilDog1978]

I am trying to match data from two different sources in my database. The
problem is one data source uses - in the part number field, the other data
source does not. I want to run a query with an expression that will ignore
certain characters in the part number field. Any help would be greatly
appreciated.

 

[Jeff Boyce]

First, figure out how you'd explain how to do this comparison to a really
confused assistant.

Now make it much clearer -- Access isn't even as smart as a really confused
assistant.

(I'm not be facetious, you will need to explain exactly how you want the
comparison done before you can explain that to Access. For example, does
"out of order" matter?)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[golfinray]

Where is the dash? Front, middle, or end?

 

[DevilDog1978]

As far as I can figure, the - is randomly placed in the part number. That is
why I am trying to figure out how to get access to ignore certain characters
in the part number field.

 

[DevilDog1978]

Ok for an example:
DB1 DB2
1328AS-01-HO1 1328AS01H01

The simplest way to explain it is I want Access to only pay attention to
letters and numbers, ignoring any special character in the line of data. Any
way to do this?

 

[Beetle]

Try the Replace function.

Replace("1328AS-01-HO1", "-", "")

will return

1328AS01HO1

In reality you would use your field name inside the Replace function
in a query like;

Replace([YourPartNumberField], "-", "")

You can find more on the Replace function in Access help.

 

[Jeff Boyce]

Sorry, but I'm not doing as well as a confused assistant ...

In the example you provided, I would not consider the first line to match
(DB1 <> DB2).

If I leave out special characters, I'd say the second line matches.

Is that right? If so, do you only ever care to leave out special
characters?

If so, one approach might be to convert every single value to a
'no-special-characters' version, then compare.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DevilDog1978]

I think we are on the same page. How would I go about converting it to a no
special characters version without losing data?

 

[DevilDog1978]

And if I want to include multiple characters in there?
Replace([Part_Num], "-,/,*,\", "")
WOuld that work?

Try the Replace function.

Replace("1328AS-01-HO1", "-", "")

will return

1328AS01HO1

In reality you would use your field name inside the Replace function
in a query like;

Replace([YourPartNumberField], "-", "")

You can find more on the Replace function in Access help.
--
_________

Sean Bailey

I am trying to match data from two different sources in my database. The
problem is one data source uses - in the part number field, the other data
source does not. I want to run a query with an expression that will ignore
certain characters in the part number field. Any help would be greatly
appreciated.

 

[Beetle]

No, that won't work. If that is the case (you have multiple possible
characters), then you are better off using a function like the one at
this link;

http://support.microsoft.com/default.aspx/kb/210537
--
_________

Sean Bailey

And if I want to include multiple characters in there?
Replace([Part_Num], "-,/,*,\", "")
WOuld that work?

Try the Replace function.

Replace("1328AS-01-HO1", "-", "")

will return

1328AS01HO1

In reality you would use your field name inside the Replace function
in a query like;

Replace([YourPartNumberField], "-", "")

You can find more on the Replace function in Access help.
--
_________

Sean Bailey

I am trying to match data from two different sources in my database. The
problem is one data source uses - in the part number field, the other data
source does not. I want to run a query with an expression that will ignore
certain characters in the part number field. Any help would be greatly
appreciated.

 

[John W. Vinson]

And if I want to include multiple characters in there?
Replace([Part_Num], "-,/,*,\", "")
WOuld that work?

If it's just a couple or three characters you can nest Replace calls:

Replace(Replace(Replace([Part_Num], "-", ""), "/", ""), "\", "")

If it's more than that I'd use a custom function as Beetle suggests.

 

[John W. Vinson]

Double, Tripple, Quadzillion consecutive occurences of a particular character
have no effect in the function I posted, since each character is evaluated
and processed
indepentedly.

Since "\", "\\\\\\" are all excluded characters guess I'm not understanding
why
multiple occurences of an excluded character would even be an issue.

And I did not suggest that they would. Not sure where you're getting that
interpretation.

My concern was that if they have more than three or four *different
characters* that they wish to exclude, the deeply nested Replace((((((())))))
would get unwieldy.

Bob

And if I want to include multiple characters in there?
Replace([Part_Num], "-,/,*,\", "")
WOuld that work?

If it's just a couple or three characters you can nest Replace calls:

Replace(Replace(Replace([Part_Num], "-", ""), "/", ""), "\", "")

If it's more than that I'd use a custom function as Beetle suggests.

 

[Hans Up]

The simplest way to explain it is I want Access to only pay attention to
letters and numbers, ignoring any special character in the line of data. Any
way to do this?

It's easy with a regular expression. This function relies on late
binding so you don't need to set a reference to use it. I left comments
in there in case you want early binding instead.

Public Function dropSpecialChars(ByVal pstrIn As String) As String
'* early binding requires reference to Microsoft VBScript
'* Regular Expressions:
'Dim objRegExp As RegExp
'Set objRegExp = New RegExp

'* use late binding:
Dim objRegExp As Object
Set objRegExp = CreateObject("vbscript.RegExp")

objRegExp.Pattern = "[^0-9a-z]"
objRegExp.Global = True
objRegExp.IgnoreCase = True

dropSpecialChars = objRegExp.Replace(pstrIn, vbNullString)

Set objRegExp = Nothing
End Function

 

[DevilDog1978]

Wont that also remove the alpha characters? I need something that will remove
anything that is not a letter or a number.

No, that won't work. If that is the case (you have multiple possible
characters), then you are better off using a function like the one at
this link;

http://support.microsoft.com/default.aspx/kb/210537
--
_________

Sean Bailey

And if I want to include multiple characters in there?
Replace([Part_Num], "-,/,*,\", "")
WOuld that work?

Try the Replace function.

Replace("1328AS-01-HO1", "-", "")

will return

1328AS01HO1

In reality you would use your field name inside the Replace function
in a query like;

Replace([YourPartNumberField], "-", "")

You can find more on the Replace function in Access help.
--
_________

Sean Bailey


:

I am trying to match data from two different sources in my database. The
problem is one data source uses - in the part number field, the other data
source does not. I want to run a query with an expression that will ignore
certain characters in the part number field. Any help would be greatly
appreciated.

 

[Jeff Boyce]

"Better" is in the ??? of the ???.

If the only criterion is speed, time 'em.

If another criterion is comprehensibility/maintainability, define how you'll
measure those, and on whom.

If another criterion is complexity (?how hard could it be?), then for whom?
A seasoned Access dinosaur such as yourself might not find it challenging,
but what about a newbie?

If you wish to discuss "better", please define your terms <g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

John Vinson,

Your deeply nested Replace((((((()))))) clunks!

Do you have a better solution?

Bob

Perhaps the difference is that I, as an Access dinosaur, prefer to program
in
A97, thus no built-in Replace() function.

My solution just clunks thru each character in the string and does what
needs
to be done. I'm sure we could time the processes and find the later
version
is quicker -- in milliseconds. In reality, don't think you could detect
the
difference.

Bob

Double, Tripple, Quadzillion consecutive occurences of a particular
character
have no effect in the function I posted, since each character is
evaluated

[quoted text clipped - 14 lines]

If it's more than that I'd use a custom function as Beetle suggests.

 

[John W. Vinson]

John Vinson,

Your deeply nested Replace((((((()))))) clunks!

I agree, and I did suggest that it should only be used one or two layers deep.

Do you have a better solution?

The one you suggested looks fine, and I saw no reason to repeat it.