I know this has got to be a simple formatting issue

[Brian Mink]

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.

[Darryl Kerkeslager]

The alternative (store the number without the dash) is easier, such as:

Format(.Fields( "off_ssn"), "###-##-####")
Format(.Fields( "off_phone" ), "(@@@) @@@-@@@@")

your numbers would be similar:

Format(.Fields( "my_data"), "@@@@-######")
Darryl Kerkeslager

"Brian Mink" <Br*******@dodg eit.com> wrote in message
news:a6******** *************** ***@posting.goo gle.com...

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.

[Salad]

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(ByVa l varString As Variant) As Variant

Dim strChar As String
Dim lngFor As Long
Dim lngAsc As Long

If Not IsNull(varStrin g) Then
For lngFor = 1 To Len(varString)
strChar = Mid(varString, lngFor, 1)
lngAsc = Asc(UCase(strCh ar))
If (lngAsc >= 65 And lngAsc <= 90) Or _
IsNumeric(strCh ar) 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.

[Brian Mink]

Thanks so much for sending that! It worked perfectly! Did you write it
just for me or did you have a similar module that you customized?
Either way, I appreciate it. This database started out as a small
project but has grown over the years. I did almost all of it using
Access tools, with only one module. It looks like it is time to learn
VBA.

Thanks again,

Brian

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.

[Alan Webb]

Brian,
Start with the simple: are the columns in both source & destination
databases the same datatype and size? Second, are you trying to import text
into a numeric column? Assuming both columns are text and both are the same
size and they are big enough, the dash should not be an issue.

"Brian Mink" <Br*******@dodg eit.com> wrote in message
news:a6******** *************** ***@posting.goo gle.com...

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.