lookup loop for text inside text field ...

[Guest]

Hi,

I'm stuck and need some guidance.

I have two tables (Tbl1 & Tbl2) with the following fields:

Tbl1: Tbl1_ID (unique number), Account# (5 digit field), TextField (255 Characters)

Tbl2: Account# (5 digit field), Reference (20 Characters), Tbl2_ID (unique number)

I want to create a VBA script (unless there is another alternative) that will:
1) for each record in Tbl1
2) look up for each Tbl1.Account# = Tbl2.Account#
3) find [Tbl2].[Reference] text inside [Tbl1].[TextField]
4) return [Tbl2].[Tbl2_ID]

Note there maybe be more than on instance. The macro should countinue to add matches until all records in Account# match have been checked. (or Null where no records are found).

I'm not sure where to start.

Any guidance would be appreciated.

 

[Rob]

You can do this with a simple query, using the InStr
function.

Place the two tables in the QBE editor and link them by
the Account# fields. For your output fields, include
Tbl2_ID in one column and this an another:

Test: InStr([TextField],[Reference])

Set the criteria for this column to <> 0, and the
resulting data set will include the only the cases you are
looking for.

In SQL, it looks like this:

SELECT Tbl2.Tbl2_ID, InStr([TextField],[Reference]) AS Test
FROM Tbl1 INNER JOIN Tbl2 ON Tbl1.[Account#] = Tbl2.
[Account#]
WHERE (((InStr([TextField],[Reference]))<>0));


Rob

-----Original Message-----
Hi,

I'm stuck and need some guidance.

I have two tables (Tbl1 & Tbl2) with the following fields:

Tbl1: Tbl1_ID (unique number), Account# (5 digit field), TextField (255 Characters)

Tbl2: Account# (5 digit field), Reference (20

Characters), Tbl2_ID (unique number)

I want to create a VBA script (unless there is another alternative) that will:
1) for each record in Tbl1
2) look up for each Tbl1.Account# = Tbl2.Account#
3) find [Tbl2].[Reference] text inside [Tbl1].[TextField]
4) return [Tbl2].[Tbl2_ID]

Note there maybe be more than on instance. The macro

should countinue to add matches until all records in
Account# match have been checked. (or Null where no
records are found).

 

[Marshall Barton]

I'm stuck and need some guidance.

I have two tables (Tbl1 & Tbl2) with the following fields:

Tbl1: Tbl1_ID (unique number), Account# (5 digit field), TextField (255 Characters)

Tbl2: Account# (5 digit field), Reference (20 Characters), Tbl2_ID (unique number)

I want to create a VBA script (unless there is another alternative) that will:
1) for each record in Tbl1
2) look up for each Tbl1.Account# = Tbl2.Account#
3) find [Tbl2].[Reference] text inside [Tbl1].[TextField]
4) return [Tbl2].[Tbl2_ID]

Note there maybe be more than on instance. The macro should countinue to add matches until all records in Account# match have been checked. (or Null where no records are found).

Rob's suggestion may be easiest to put together using the
QBE, but if you switch to SQL view you can use a "non-equi"
Join

SELECT Tbl2.Tbl2_ID,
FROM Tbl1 INNER JOIN Tbl2
ON Tbl1.[Account#] = Tbl2.[Account#]
AND TextField.TextField Like "*" & Tbl1.Reference & "*"

 

[Guest]

Thanks,

Both look great and just what I want...

thanks again,

Marcus