link question

I put a linked table to SQL Server in an Access 2003 database. The field I
want to link on in the SQL db is a social security number without the dashes
(123456780) while the SSN in the Access table is 123-45-6780. I tried
using the MID function to parse the linked fields and retrieve another field
from the SQL database based on the SSN.

Does anyone have any ideas on a good way to do this?

Thanks

Are both fields text? You can remove the "-" from a field/value using
Replace([SSN],"-","")

--
Duane Hookom
MS Access MVP


"Bill" wrote in message
news
I put a linked table to SQL Server in an Access 2003 database. The field
I
want to link on in the SQL db is a social security number without the
dashes
(123456780) while the SSN in the Access table is 123-45-6780. I tried
using the MID function to parse the linked fields and retrieve another
field
from the SQL database based on the SSN.

Does anyone have any ideas on a good way to do this?

Thanks

Yes, both fields are text. So where would I perform this replace? I don't
really want to change the actual data but retrieve a field from the linked
SQL Server table based on the SSN join. I was trying to use a lookup field
in the Access table using an SQL query for the record source. I'm not sure
if I'm on the right track though.

Thanks.


"Duane Hookom" wrote in message
...
Are both fields text? You can remove the "-" from a field/value using
Replace([SSN],"-","")

--
Duane Hookom
MS Access MVP


"Bill" wrote in message
news
I put a linked table to SQL Server in an Access 2003 database. The
field
I
want to link on in the SQL db is a social security number without the
dashes
(123456780) while the SSN in the Access table is 123-45-6780. I tried
using the MID function to parse the linked fields and retrieve another
field
from the SQL database based on the SSN.

Does anyone have any ideas on a good way to do this?

Thanks

You can add both tables to your query and set the criteria under the field
without the "-" to
=Replace([SSNwith-],"-","")

--
Duane Hookom
MS Access MVP


"Bill" wrote in message
. ..
Yes, both fields are text. So where would I perform this replace? I
don't
really want to change the actual data but retrieve a field from the linked
SQL Server table based on the SSN join. I was trying to use a lookup
field
in the Access table using an SQL query for the record source. I'm not
sure
if I'm on the right track though.

Thanks.


"Duane Hookom" wrote in message
...
Are both fields text? You can remove the "-" from a field/value using
Replace([SSN],"-","")

--
Duane Hookom
MS Access MVP


"Bill" wrote in message
news
I put a linked table to SQL Server in an Access 2003 database. The
field
I
want to link on in the SQL db is a social security number without the
dashes
(123456780) while the SSN in the Access table is 123-45-6780. I
tried
using the MID function to parse the linked fields and retrieve another
field
from the SQL database based on the SSN.

Does anyone have any ideas on a good way to do this?

Thanks