HELP Appreciated

[hdfixitup]

I have two text fields storing part numbers. One stores a stripped number
and the other stores a spaced number. Example: (12345 and 12 34 5). I have a
Search text box that I currently type a stripped number into and the record
is retrieved. I need help on the following:
If a spaced number is entered, I would like to strip all spaces and have it
find the stripped number as if I had typed the stripped number first. can
anyone help me? Thanks in advance.

hdfixitup

 

[raskew via AccessMonster.com]

Hi -

Try this:

Function fFixString(pstr As String) As String
'*******************************************
'purpose: Remove spaces (chr(32)) and
' hyphens (chr(45)) from a string
' and returns result in vbUpper
' format
'coded by: raskew
'Inputs: from debug (immediate) window
' ? fFixString("7L24-3a719-AD ")
'Output: 7L243A719AD
'*******************************************

Dim strHold As String
Dim strDump As String
Dim i As Integer
Dim n As Integer

strHold = Trim(pstr)

'1) Make strHold upper case
strHold = StrConv(strHold, vbUpperCase)

'2) Eliminate spaces and hyphens.
'Note if other characters must be removed, increase
'n and add the character to the Choose() statement
For n = 1 To 3
strDump = Choose(n, Chr(45), Chr(32))
Do While InStr(strHold, strDump) > 0
i = InStr(strHold, strDump)
strHold = Left(strHold, i - 1) & Mid(strHold, i + 1)
Loop
Next n
fFixString = strHold

End Function

HTH - Bob

 

[hdfixitup]

I believe I am almost there. I had trouble attaching your function to the
After Update event on my form. I added an equal sign and parenthesis and got
errors.

Right now, I have the following function attached as =Find_Record()

Function Find_Record()
On Error GoTo Find_Record_Err

With CodeContextObject
DoCmd.RunCommand acCmdRemoveFilterSort
DoCmd.GoToControl "[NumberID]"
DoCmd.FindRecord .Search, acEntire, False, , False, , True
DoCmd.GoToControl "Number"

End With

Find_Record_Exit:
Exit Function

Find_Record_Err:
MsgBox Err.Description
Resume Find_Record_Exit

End Function

How might I incorporate my last commands into your function and get it to
work?

Thank you in advance for your help as i think i am almost there.

hdfixitup