Find text in String for Update query criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create an Update Query. I have the following tables:

Table1
Accts Item ID
Acct1 01
Acct2 04
Acct3 01
Acct4 03

Table2
ID Name Items
B23 Pam 01,05,09
B35 Amy 02,06,10
A72 Frank 03,07,11
Z53 Dan 04,08,12

I want to update [Table1]![ID] with the [Table2]![ID] where the
[Table1]![Item] is in the string [Table2]![Items]. Both fields are text
fields so there should not be any data-type problems but I just don’t know
what to do next!
 
Create a query based on table1 with the 3 columns being Accts, Item and ID.
Change the query type to an update query and in the 'Update To' row of the
ID column put this and then run the query
DLookUp("ID","Table2","Items Like '*" & [Item] & "*'")
 
Worked like a charm!! Thanks!

Dennis said:
Create a query based on table1 with the 3 columns being Accts, Item and ID.
Change the query type to an update query and in the 'Update To' row of the
ID column put this and then run the query
DLookUp("ID","Table2","Items Like '*" & [Item] & "*'")

Molasses26 said:
I am trying to create an Update Query. I have the following tables:

Table1
Accts Item ID
Acct1 01
Acct2 04
Acct3 01
Acct4 03

Table2
ID Name Items
B23 Pam 01,05,09
B35 Amy 02,06,10
A72 Frank 03,07,11
Z53 Dan 04,08,12

I want to update [Table1]![ID] with the [Table2]![ID] where the
[Table1]![Item] is in the string [Table2]![Items]. Both fields are text
fields so there should not be any data-type problems but I just don’t know
what to do next!
Click to expand...
Click to expand...
 
Back
Top