strip dashs in query

[LOIS]

I have two files with Invoice numbers. One file has "INVNUMBER"
entered as
695-09545 (for example) and the other file has 69509545. I need to
compare the freight amounts and select the ones with differing
amounts. (In some cases the freight amounts might be larger and I need
the larger amounts) I need to either
strip the dash out of the one file or enter the dash into the other
file so they will match up. I tried altering a macro I found but :I
obviously can not
figure out how to put it into the query or how to run it.
I am a newbie and I am not sure if this is what I want or not.
I would greatly appreciate any help that you can give me.

Thanks in Advance
Lois


Option Compare Database
Option Explicit

' --------------------------------------------------
' Function StripString()
'
' Returns a string minus a set of specified chars.
' --------------------------------------------------
Function StripString(INVNUMBER As Variant) As Variant
On Error GoTo StripStringError

Dim strChar As String, strHoldString As String
Dim i As Integer

' Exit if the passed value is null.
If IsNull(INVNUMBER) Then Exit Function

' Exit if the passed value is not a string.
If VarType(INVNUMBER) <> 8 Then Exit Function

' Check each value for invalid characters.
For i = 1 To Len(INVNUMBER)
strChar = Mid$(INVNUMBER, i, 1)
Select Case strChar
Case ".", "#", ",", "-"
' Do nothing
Case Else
strHoldString = strHoldString & strChar
End Select
Next i

' Pass back corrected string.
StripString = strHoldString

StripStringEnd:
Exit Function

StripStringError:
MsgBox Error$
Resume StripStringEnd
End Function

 

[RobFMS]

In your query, do the following:

<field1>, ... <fieldx>, Replace(InvNumber, "-", "") as [Invoice Number],
<fieldy>, ....

HTH
--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-