Solved

Help with Access SQL Statement

Posted on 2009-05-07
3
227 Views
Last Modified: 2012-05-06
I have an event procedure within an Access application that has the following query:

DoCmd.RunSQL "Delete * FROM [Payments] WHERE [Payments].OrderID = [Orders].OrderID AND [Orders].CustomerID = " & CustomerNumber & ";"

When it gets to this statement, it keeps prompting me for "[Orders].OrderID".  Orders is a table in the database, and OrderID is the Primary key in that table.

for the life of me I cannot see what is wrong with this statement.

Can someone please help.

Thanks...Tom
0
Comment
Question by:tpigielski
3 Comments
 
LVL 13

Expert Comment

by:usachrisk1983
ID: 24332752
But it doesn't know what [orders].orderid is.  I suspect that it's a field on your form somewhere?  If so, use the control name instead.  You'll need to do the same for the other items in your WHERE clause.
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 250 total points
ID: 24332789
Hi tpigielski,

Is this what you want?

DoCmd.RunSQL "Delete * FROM [Payments] WHERE [Payments].OrderID IN (SELECT [Orders].OrderID FROM [Orders] WHERE [Orders].CustomerID = " & CustomerNumber & ");"


lwadwell
0
 
LVL 6

Expert Comment

by:BALMUKUND KESHAV
ID: 24333137
Do hope there must be a textbox/combo box having data of orders.orderid , customerid, put that control instead of orders.orderid, if your textbox/combo box havign data of orders,orderid is txtorderid and customerid is txtcustomerid, then use the sql qry like this :

DoCmd.RunSQL "Delete * FROM [Payments] WHERE [Payments].OrderID = txtorderid AND [Payments].customerid = " & CustomerNumber & ";"

Bm keshav
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now