Solved

Help with Access SQL Statement

Posted on 2009-05-07
3
226 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

16 Experts available now in Live!

Get 1:1 Help Now