string comparsion using access--help

[Cheryl]

Hi
I using access 2003. I am trying to compare data from two tables. Table A
and Table B. Table A has 12 digits in 1 column and table b has 10 digits in
1 column. I need to compare Table A data starting in position 2 and ending
in positon 11. I tried using instr function but it did not work.

SELECT A.ItemLookupCode, B.ItemLookupCode
FROM A, B
WHERE InStr(2, A.ItemLookupCode, B.ItemLookupCode,11, COMPARE)



Thanks

Cbs

 

[zionsaal]

Hi
I using access 2003. I am trying to compare data from two tables. Table A
and Table B. Table A has 12 digits in 1 column and table b has 10 digits in
1 column. I need to compare Table A data starting in position 2 and ending
in positon 11. I tried using instr function but it did not work.

SELECT A.ItemLookupCode, B.ItemLookupCode
FROM A, B
WHERE InStr(2, A.ItemLookupCode, B.ItemLookupCode,11, COMPARE)

Thanks

Cbs

use the "mid()" function

 

[Douglas J. Steele]

Your syntax for InStr is incorrect (and it's not the correct function
anyhow).

SELECT A.ItemLookupCode, B.ItemLookupCode
FROM A INNER JOIN B
WHERE Mid(A.ItemLookupCode, 2, 10) = B.ItemLookupCode