Brian Mink wrote:
I am having trouble getting one database to export data that another
one can import. The problem is a dash "-". I have a group of numbers
entered in my database that have a dash in them (e.g. "AB1-234567")
but the database that I am sending the data to cannot accept those
dashes and needs the input set accordingly (e.g. "AB1234567"). Is
there a way to have a query or report not display those dashes? All
numbers are entered as 10 digits (counting the dash) and the dash is
always the 4th character. Alternatively, if it is easier to do it the
other way around, if the number was entered without the dash, is there
a way to make it appear to have the dash on reports and forms?
I am using Access XP (2002) but I expect the solution would be the
same for any version.
There may be a function like Replace or somthing like that where you can
replace chars in a string with another value with your current version
of Access. Here is a simple routine to strip chars in a string (allows
a Null to be passed) that removes anything not an A-Z or 0-9 that I can
use in A97. You can drop this code in a Module.
Public Function StripChars(ByVal varString As Variant) As Variant
Dim strChar As String
Dim lngFor As Long
Dim lngAsc As Long
If Not IsNull(varString) Then
For lngFor = 1 To Len(varString)
strChar = Mid(varString, lngFor, 1)
lngAsc = Asc(UCase(strChar))
If (lngAsc >= 65 And lngAsc <= 90) Or _
IsNumeric(strChar) Then
StripChars = StripChars & strChar
End If
Next
End If
End Function
You can then create a column in your query. Something like
FieldNoDashes : StripChars([FieldWithDashes])
This will strip the field name FieldWithDashes (substitue your table
field name) that contains the dashes and return a value with no dashes.
