Re: remove alpha content in text field

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: vw (anonymous_at_discussions.microsoft.com)
Date: 07/22/04 

Date: Wed, 21 Jul 2004 23:03:20 -0700

Thks for this. Alpha is at beginning of field so what
you've sent should suffice.
Regds
>-----Original Message-----
>On Wed, 21 Jul 2004 18:00:50 -0700, vw wrote:
>
>> Is there an existing command (ie an easy way) to
remove
>> alpha entries in an existing alpha-numeric field. EG
I
>> want to see results where original field entry is
AB1234,
>> result field is 1234.
>> Any ideas?
>
>If the number was always at the beginning of the field,
i.e. 1234AB,
>you could use in a query:
>Resultfield:Val([FieldName])
>
>But as long as the numbers can be anywhere within the
field you'll
>need to check each character.
>If the numbers in the field are going to be all together
(AB1234) as
>in your sample data, and not mixed up within the field
(ab4cv5k6) the
>following will work for you.
>
>You need to create a user defined function to cycle
through the value
>in each record until you find the first number character:
>
>In a Module:
>
>Function ValNumber(strString As String) As Integer
>On Error GoTo Err_Handler
>
>Dim intX As Integer
>Dim intY As Integer
>intY = Asc(strString)
>Do While intY < 48 Or intY > 57
>intX = intX + 1
>If intX = Len(strString) Then Exit Do
>intY = Asc(Mid(strString, intX, 1))
>Loop
>If intX = 0 Then intX = 1
>
>ValNumber = Val(Mid(strString, intX))
>
>Exit_ValNumber:
> Exit Function
>Err_Handler:
>Resume Exit_ValNumber
>
>End Function
>===
>You can call it from a query:
>Result:ValNumber([FieldName])
>
>As criteria in the query use:
>Where [FieldName] is not null
>
>If the numbers are mixed up within the field, i.e.
ab4kl6fv9, the
>method would be similar to the above, but you would have
to cycle
>through each character in the string and move the
numbers to a
>different string.
>If that's the case, and you can't figure out how, post
back and I'll
>send the code along.
>
>--
>Fred
>Please only reply to this newsgroup.
>I do not reply to personal email.
>.
>

Relevant Pages

  • Re: "Or" option using INSTR() Function?
    ... > position of the first non-numeric character in a string. ... Public Function NonNumber(FieldIn as String) ... Dim intX As Integer ...
    (microsoft.public.access.queries)
  • Re: Update Table - One field - two expressions?
    ... You should be able to do that all in one query ... then trims off the first character from the resulting string. ... update the same field twice in one query. ...
    (microsoft.public.access.queries)
  • Re: Stripping Characters in a String
    ... Create a User Defined function and call it from a query. ... Public Function FindAllNumbers(strString As String) ... Dim intX As Integer ... Dim intY As Integer ...
    (microsoft.public.access.forms)
  • Re: Remove characters in an ASCII decimal range: How To?
    ... And it doesn't work to concatenate a null string, it has to be a string of one or more characters. ... 'This string has an embedded'+CHAR+'null character'; ... I'm interested in using your query, but as I am only vaguely familiar ... with FOR XML PATH, I am not sure how to process the results. ...
    (microsoft.public.sqlserver.programming)
  • Undefined Function Error on custom function
    ... particular character is in a given string, and return the location in the ... I've used this function in an update query and have no problem executing the ...
    (microsoft.public.access.modulesdaovba)