Prev: ciao
Next: link acces to a web document
From: Lori on 5 Mar 2010 16:29
Okay, I have accountants driving me crazy. They are creating duplicate
entries in our accounting program and I would like our database to be able to
pull out blank spaces, commas, dashes, etc. They have a habit of entering an
invoice number as INV-123 or INV 123 and the accounting system isn't catching
it because of the non-alpha numeric characters.

What would be the most efficient way of accomplishing this?
From: KARL DEWEY on 5 Mar 2010 17:30
You could run an Update query using replace function --
Replace(Replace(Replace([YourField], "-", ""), " ", ""), ",", "")))

You can add on to it with other character.

--
Build a little, test a little.


"Lori" wrote:

> Okay, I have accountants driving me crazy. They are creating duplicate
> entries in our accounting program and I would like our database to be able to
> pull out blank spaces, commas, dashes, etc. They have a habit of entering an
> invoice number as INV-123 or INV 123 and the accounting system isn't catching
> it because of the non-alpha numeric characters.
>
> What would be the most efficient way of accomplishing this?
From: Ken Snell on 5 Mar 2010 17:35
Define "non-alphanumeric characters"? You mean any character that is not A-Z
or 0-9? If yes, this function will do what you seek:



'**************************************
'* *
'* Fxn StripAllNonLetterNumericChars *
'* *
'**************************************

' ** This function strips all nonletter and nonnumeric characters from a
text string.

Function StripAllNonLetterNumericChars(varOriginalString As Variant) As
String
Dim blnStrip As Boolean
Dim intLoop As Integer
Dim lngLoop As Long
Dim strTemp As String, strChar As String
Dim strOriginalString As String
On Error Resume Next
strTemp = ""
strOriginalString = Nz(varOriginalString, "")
For lngLoop = Len(strOriginalString) To 1 Step -1
blnStrip = True
strChar = Mid(strOriginalString, lngLoop, 1)
If strChar Like "[a-z0-9]" Then blnStrip = False
If blnStrip = False Then strTemp = strChar & strTemp
Next lngLoop
StripAllNonLetterNumericChars = strTemp
Exit Function
End Function



--

Ken Snell
http://www.accessmvp.com/KDSnell/


"Lori" <Lori(a)discussions.microsoft.com> wrote in message
news:7D809B1F-81BE-4625-99E8-DCB557DEA476(a)microsoft.com...
> Okay, I have accountants driving me crazy. They are creating duplicate
> entries in our accounting program and I would like our database to be able
> to
> pull out blank spaces, commas, dashes, etc. They have a habit of entering
> an
> invoice number as INV-123 or INV 123 and the accounting system isn't
> catching
> it because of the non-alpha numeric characters.
>
> What would be the most efficient way of accomplishing this?


From: De Jager on 13 Mar 2010 12:43

"Lori" <Lori(a)discussions.microsoft.com> wrote in message
news:7D809B1F-81BE-4625-99E8-DCB557DEA476(a)microsoft.com...
> Okay, I have accountants driving me crazy. They are creating duplicate
> entries in our accounting program and I would like our database to be able
> to
> pull out blank spaces, commas, dashes, etc. They have a habit of entering
> an
> invoice number as INV-123 or INV 123 and the accounting system isn't
> catching
> it because of the non-alpha numeric characters.
>
> What would be the most efficient way of accomplishing this?

From: joelgeraldine on 17 Mar 2010 09:47
jkjkjkj

"Lori" <Lori(a)discussions.microsoft.com> a écrit dans le message de groupe de
discussion : 7D809B1F-81BE-4625-99E8-DCB557DEA476(a)microsoft.com...
> Okay, I have accountants driving me crazy. They are creating duplicate
> entries in our accounting program and I would like our database to be able
> to
> pull out blank spaces, commas, dashes, etc. They have a habit of entering
> an
> invoice number as INV-123 or INV 123 and the accounting system isn't
> catching
> it because of the non-alpha numeric characters.
>
> What would be the most efficient way of accomplishing this?

 | 
Pages: 1
Prev: ciao
Next: link acces to a web document