leading dash in phone number 1

[olypost]

I've imported a sql server table into an access database.
When I attempt to query a form that points to this imported
data, all the columns and rows appear correctly, except for some reason the phone number column value shows a leading dash in front of the number.

I believe the phone number column in sql server is text, and (i think) so is the same named phone number column in access.

sql server shows: (800)555-1212
the access screen shows: -(800)555-1212

What is causing the leading dash to appear? What do I need to do to get rid of it? The access form is a read-only application.

I haven't seen the format or input mask for the phone number field in access--could this cause the dash? There is a replica access table in which the phone numbers queries up okay--but I'm not sure what any differences in datatypes there are.

I've strip out the left and right paranthesis in the area code--still the dash appears.

Is it a datatype issue from sql server?

I'm brand new to access so any help would be appreciated.

Thanks

 

[TheAceMan1]

How are ya olypost . . . . .

Hard to tell with info supplied, but you can remove the dash with an update query on the field. Just set [blue]Update To:[/blue] as follows:

[blue]Right([FieldName], 13)[/blue]

[blue]This is good when parentheses are included[/blue], if not, change the count to [blue]11[/blue].

See Ya! . . . . . .

 

[olypost]

Thanks.

The sql server data is a view that I wrote to pull together data to make it match the schema and rows of the equivalent access table data. There's an existing access form that queries this data, but we want to query on sql server data for viewing purposes only. So I can't update in access at all, after importing.

When I view the data in access after the import, I don't see the leading dash. It's only when the rows are queried in the form that I see it.

 

[TheAceMan1]

[blue]There's an existing access form that queries this data, but we want to query on sql server data for [purple]viewing purposes only[/purple].[/blue]

Since the form is for [purple]viewing purposes only[/purple], why not add a custom field:

[blue] fixPhone:Right([ActualPhoneFieldName], 13)[/blue]

[purple]and display the custom field in the form instead . . .[/purple]

See Ya! . . . . . .

 

[PHV]

It's only when the rows are queried in the form that I see it
What is the Format property of the phone number control ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244