link question

[Bill]

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

 

[Duane Hookom]

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

 

[Bill]

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.

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

--
Duane Hookom
MS Access MVP

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

 

[Duane Hookom]

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

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.

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

--
Duane Hookom
MS Access MVP

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