geisterfurz007 geisterfurz007 - 3 months ago 22
SQL Question

Access Check If DataSet contains string via vba Macro

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"


1st_Table.ID_Var would contain the ID of the row of the first table
Now the more or less obvious question: How do I accomplish that?
This is bascally the first time working with VBA/Access AND SQL so I have no closer thoughts on how to do that.

Thanks in advance

Greetings

geisterfurz007

P.S. The variable names will be changed in the final version; they are just to visualize. Just saw that they were colored by SO.

Answer

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.