By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,767 Members | 1,073 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,767 IT Pros & Developers. It's quick & easy.

Problem with Macro RunSql Statement

P: 83
I would like a all records from [Master Log] (a table) where [Master Log].[Disposition]="Rejected" and input those records into the [Rejected] (a table). Both tables are exactly the same as far as structure. I have created a macro with one RunSql Statement.
[code]
INSERT INTO [Rejected] SELECT ALL FROM [Master Log] WHERE [Master Log].[Disposition]="Rejected"
[Code/]
It says there is a syntax error, do you have any ideas?
Also, could I run this from a form, or do I have to create a macro in order to do it.
Actually, I want to then delete the "rejected" records from the master log. Would I create another runsql statement? How might it look?
Jun 5 '07 #1
Share this Question
Share on Google+
3 Replies

LacrosseB0ss
100+
P: 113
With the insert statement, you need a VALUES clause to specify what will go in. And "ALL" will look for a field name. To select every column from a matching row use '*'. From what I'm seeing, everything is ok except those. Your new query will look something like this:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [Rejected] 
  2. VALUES (SELECT * FROM [Master Log] WHERE [Master Log].[Disposition]="Rejected")
  3.  
Now, your 2nd question (remove records in Rejected), you will need another query. The best query to use for this would look as follows:

Expand|Select|Wrap|Line Numbers
  1. DELETE 
  2. FROM Master Log 
  3. WHERE "primaryKey" NOT IN (SELECT "primaryKey" FROM Rejected)
  4.  
Hope this works/helps
- LB
Jun 5 '07 #2

P: 83
Thank you, but I get a syntax error when i place the statement in the macro run sql action?

With the insert statement, you need a VALUES clause to specify what will go in. And "ALL" will look for a field name. To select every column from a matching row use '*'. From what I'm seeing, everything is ok except those. Your new query will look something like this:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [Rejected] 
  2. VALUES (SELECT * FROM [Master Log] WHERE [Master Log].[Disposition]="Rejected")
  3.  
Now, your 2nd question (remove records in Rejected), you will need another query. The best query to use for this would look as follows:

Expand|Select|Wrap|Line Numbers
  1. DELETE 
  2. FROM Master Log 
  3. WHERE "primaryKey" NOT IN (SELECT "primaryKey" FROM Rejected)
  4.  
Hope this works/helps
- LB
Jun 5 '07 #3

LacrosseB0ss
100+
P: 113
The problem is with your table name. Master (space) Log is confusing to the query. It assumes Master is the table name and Log is another command. However, there is no command named Log.

I popped the query into an access db and changed the table name to Master_Log and everything worked fine.

Hope this helps
- LB
Jun 5 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.