Hello Stackoverflow community!
I am currently confronted with writing somthing, that automatically cleans up a database after certain requirements. Here is what I have to do:
If a cell in one table contains a certain substr (lets say "Mrs."), in another table a Togglebox is supposed to be checked (Yes if "Mrs." is contained and No if not)
I already worked out something that sets The Toggleboxes to -1 in that column, but I have to include a check, if it even has to be changed and in which row. For that I wanted to bring up an If-construction and a variable that would contain the ID of the first table's row to determine, which of the Rows in the second table have to be changed.
So the thought is like this:
If 1st_Table contains "Mrs." Then
CurrentDb.Execute "UPDATE 2nd_Table SET gender = -1 WHERE foo = 1st_Table.ID_Var"
Try this (SQL update query):
UPDATE 2nd_Table INNER JOIN 1st_Table ON 2nd_Table.ID = 1st_Table.ID SET 2nd_Table.gender = -1 WHERE 1st_Table.PersonName LIKE '*Mrs.*'
ID -> reference between both tables PersonName -> column in 1st table containing Mrs.