Update Query help please 1

[Bill4tektips]

I am trying to create an Update Query that pulls the information from one table and if the criteria is found in the second table deletes it.
tblFind_Data is a one field table fed from a Search box on a form and tblBMS_Docs_From has 42 fields containing numbers in the format BS BP.0001 or 3.1.2. I want the update query to check tblBMS_Docs_From and if there is criteria that matches tblFind_Data then delete it.
The sql I have tried at the moment is:
UPDATE tblFind_Data, tblBMS_Docs_From SET tblBMS_Docs_From.[01] = "Null", tblBMS_Docs_From.[02] = "Null", tblBMS_Docs_From.[03] = "Null", tblBMS_Docs_From.[04] = "Null", tblBMS_Docs_From.[05] = "Null", tblBMS_Docs_From.[06] = "Null", tblBMS_Docs_From.[07] = "Null", tblBMS_Docs_From.[08] = "Null"
WHERE (((tblBMS_Docs_From.[01])=[tblFind_Data].[data]) AND ((tblBMS_Docs_From.[02])=[tblFind_Data].[data]) AND ((tblBMS_Docs_From.[03])=[tblFind_Data].[data]) AND ((tblBMS_Docs_From.[04])=[tblFind_Data].[data]) AND ((tblBMS_Docs_From.[05])=[tblFind_Data].[data]) AND ((tblBMS_Docs_From.[06])=[tblFind_Data].[data]) AND ((tblBMS_Docs_From.[07])=[tblFind_Data].[data]) AND ((tblBMS_Docs_From.[08])=[tblFind_Data].[data]));
I have only done Fields 1 - 8 at the moment to try it.
Can anyone help?

 

[Andrzejek]

Are you trying to write a word "null" into some of your fields or are you trying to set them to NULL (no value)?

If you want to SET them to NULL, you need:

...
SET tblBMS_Docs_From.[01] = NULL, 
    tblBMS_Docs_From.[02] = NULL, 
    tblBMS_Docs_From.[03] = NULL, ...

Have fun.

---- Andy

 

[Bill4tektips]

Still does not work

 

[dhookom]

Still does not work

Replies like this should be banned from all news groups. The least you could do is:

- provide the SQL you attempted
- provide the message or results that suggests what doesn't work
- ask for more assistance
- thank the person for attempting to help

I doubt any query will be updateable when you don't have a join on a primary/foreign key. Try build a select query and see if you can manually edit any values.

Also, your table structure seems highly un-normalized. IMO, helping you continue with a solution that might not be based on sound tables is not adviseable.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

My suggestion was not a solution.

If your Update statement looks like this:

UPDATE [red]tblFind_Data[/red], tblBMS_Docs_From 
SET tblBMS_Docs_From.[01] = NULL, 
tblBMS_Docs_From.[02] = NULL, 
tblBMS_Docs_From.[03] = NULL, 
tblBMS_Docs_From.[04] = NULL, 
tblBMS_Docs_From.[05] = NULL, 
tblBMS_Docs_From.[06] = NULL, 
tblBMS_Docs_From.[07] = NULL, 
tblBMS_Docs_From.[08] = NULL
WHERE (((tblBMS_Docs_From.[01])=[tblFind_Data].[data]) 
AND ((tblBMS_Docs_From.[02])=[tblFind_Data].[data]) 
AND ((tblBMS_Docs_From.[03])=[tblFind_Data].[data]) 
AND ((tblBMS_Docs_From.[04])=[tblFind_Data].[data]) 
AND ((tblBMS_Docs_From.[05])=[tblFind_Data].[data]) 
AND ((tblBMS_Docs_From.[06])=[tblFind_Data].[data]) 
AND ((tblBMS_Docs_From.[07])=[tblFind_Data].[data]) 
AND ((tblBMS_Docs_From.[08])=[tblFind_Data].[data]));

You Update [tt]tblBMS_Docs_From table[/tt], right?

If so, why do you have [tt]tblFind_Data[/tt] table in this part of your Update statement (RED part)?

Have fun.

---- Andy

 

[Bill4tektips]

Andrezjek, thank you for your assistance, I have got it working now. Nothing like missing the obvious.