469,637 Members | 1,597 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,637 developers. It's quick & easy.

Correctly format where statement in query

imrosie
222 100+
Hello Experts,

It's going to take one to figure this out. My Order form is supported by a query of two tables (Customers and Orders). There is a subform within the Form for entering in the new Order infor, so I need both tables in the query. A customer is first selected through a combo box on the Order frm to begin new Order. Unfortunately, I can't bring up a newly added customer name IF that Customer doesn't have a prior order. Otherwise the combo can bring up any customer. Here's the SQL statement:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Customers.FirstName, Customers.LastName, Orders.OrderDate, Orders.ShipDate, Orders.FreightCharge, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipStateOrProvince, Orders.ShipZIPCode, Orders.ShipCountry, Customers.CompanyName, Customers.BillingAddress, Customers.City, Customers.StateOrProvince, Customers.ZIPCode, Customers.Country, Customers.ContactTitle, Orders.ShipPhoneNumber, Orders.ShipFaxNumber, Orders.PurchaseOrderNumber, Customers.Notes FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
What happens is when a new customer is selected, the cursor skips the first three controls and jumps to the 4th (the first 3 are , firstname, lastname, customerid). Those fields match the row query for selecting a customer i
Expand|Select|Wrap|Line Numbers
  1. SELECT Customers.CustomerID, [FirstName] & " " & [LastName] FROM Customers ORDER BY [FirstName] & "," & [LastName]; 
n the combo box:

This same combo box has also an AfterUpdate event:
Expand|Select|Wrap|Line Numbers
  1. Private Sub custname_AfterUpdate()
  2. Private Sub custname_AfterUpdate()
  3. Me.FilterOn = True
  4. Me.Filter = "CustomerID = " & Me.custname
  5. Me.Requery
  6. End Sub
  7.  

I think I need a Where statement that will allow me to bring up a customer when there isn't a prior order associated. Can anyone help me formulate one? Or if I'm wrong on my assumption, point me in the right direction. I tried a left join instead and it didn't work. thanks in advance

Rosie
Aug 25 '07 #1
9 1303
imrosie
222 100+
After reading my post I want to make clear the set up for the Main form. It has a subform to capture OrdersDetails(quantity, unit, price,,ect. and Products info) . But the controls on the main form capture data for the Orders table (such as Orders subtotal, Orders total, Order date, ect) and for the Customer tables (Customer name, Shipping details, etc.). Both get updated through that query for main form.

thanks
Rosie
Aug 25 '07 #2
Scott Price
1,384 Expert 1GB
Good morning Rosie!

You can try exploring a right or left join between your two tables in the original select query. Open the query in design view, double click on the line representing the join, it will bring up a dialog box with three options. Choose the one that says Show ALL records from table Customer and only those records from table Orders where the joined fields match (off the top of my head, here so the exact wording there might vary.)

There are a few ? marks in your code, but if it's working fine for you as is, I'll not bring them up :-)

Regards,
Scott
Aug 25 '07 #3
imrosie
222 100+
Good morning Rosie!

You can try exploring a right or left join between your two tables in the original select query. Open the query in design view, double click on the line representing the join, it will bring up a dialog box with three options. Choose the one that says Show ALL records from table Customer and only those records from table Orders where the joined fields match (off the top of my head, here so the exact wording there might vary.)

There are a few ? marks in your code, but if it's working fine for you as is, I'll not bring them up :-)

Regards,
Scott
Scott,

Hello and thanks for your comments....I continued trying to figure out why my left join didn't work....well I figured it out and now this is working great...

What happened was that I had the CustomerID coming from the Order table...I don't know how I kept overlooking that. Anyway, its working.
take care
Rosie
Aug 26 '07 #4
Scott Price
1,384 Expert 1GB
Glad to hear it's working for you, Rosie, and thanks for posting back with what you did to make it work!

Regards,
Scott
Aug 26 '07 #5
imrosie
222 100+
Glad to hear it's working for you, Rosie, and thanks for posting back with what you did to make it work!

Regards,
Scott
Hi Scott,

You're welcome.....I've noticed now I have a bit of a problem though. Since my Left Join is working beautifully, it's has broken two other features on my form.
1.) The control for OrderID (Orders) based on an autonumber too (it's part of the right side table of the Left Join), won't give me an Autonumber when I search a customer for the start of a new Order. Before (with Original Query) I would automatically get a new Autonumber (used for OrderID) in that control for the customer name in the combo box.

2.)I have a command button on the Form called "beginanorder". It's purpose was to retain customer information, while clearing old order info. When a customer was located through combo box, the old Order also populates the controls, so this command button worked like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub beginanorder_Click()
  2. 'Copies fields from original record to variables
  3. Field1 = Me.custacct
  4. Field2 = Me.atitle
  5. Field3 = Me.fname
  6. Field4 = Me.lname
  7. Field5 = Me.TheCompany
  8.  
  9. 'Begins a new record
  10. DoCmd.GoToRecord , , acNewRec
  11.  
  12. 'Plugs in old values into the new record
  13. Me.custacct.Value = Field1
  14. Me.atitle.Value = Field2
  15. Me.fname.Value = Field3
  16. Me.lname.Value = Field4
  17. Me.TheCompany.Value = Field5
  18. End Sub
  19.  
I could really use some great ideas on this one....I'm diligently trying to figure this one out. thanks so much
Rosie
Aug 27 '07 #6
Scott Price
1,384 Expert 1GB
Are we talking about the main form now, or the subform?

What is your subform based on and what does it display?

In my mind (correct me if I'm wrong, since I sometimes make unwarranted assumptions): I see your main form having Customer information, I see your subform having Order information. The two will be joined by a common CustomerID control. (seance quality narrative here, eh? :-) Main form is based on the Left-joined query that brings up all customer information, and subform is based on separate query that brings up the order information.

How am I doing so far?

Basing them both on the same query won't work for the reasons that you are experiencing.

As for the second problem you mentioned, you didn't say what it was doing wrong? An error message?

Regards,
Scott
Aug 27 '07 #7
imrosie
222 100+
Are we talking about the main form now, or the subform?

What is your subform based on and what does it display?

In my mind (correct me if I'm wrong, since I sometimes make unwarranted assumptions): I see your main form having Customer information, I see your subform having Order information. The two will be joined by a common CustomerID control. (seance quality narrative here, eh? :-) Main form is based on the Left-joined query that brings up all customer information, and subform is based on separate query that brings up the order information.

Errors:
On 1.) I simply get the Customer information, but no Orders info, such as that "OrderID" which is based on an autonum.

On 2.) Once I bring up a particu;lar customer, I click the "beginanorder" to clear out an Old order. I get this msg:
Error '2448', you can't assign a value to this object. It's referring to line number 13 in the code above for cmd(click) "beginanorder".

From what you've said so far Scott, it sounds like you're suggesting I figure out how to make the subform do Orders and Order Details??!.Is this right? (How am I doing?)

Rosie

How am I doing so far?

Basing them both on the same query won't work for the reasons that you are experiencing.

As for the second problem you mentioned, you didn't say what it was doing wrong? An error message?

Regards,
Scott
Yes Scott, we're talking Main Orders form here. Yes, it has the Customer information. It is now based on the 'left-joined query (qryOrdersSource) that includes the Customer and Orders fields. Yes, these tables are joined by the "CustomerID".

My subform within the Main Orders form is called "Orders Detail Subform". It is based on the "Orders Details" table (not a query). This table contains the fields; productid, quantity, unitprice, discount, orderid. It displays the dropdown list with products to choose, shows the quantity selected, discount, unitprice and extended price.
Aug 28 '07 #8
Scott Price
1,384 Expert 1GB
Yes Scott, we're talking Main Orders form here. Yes, it has the Customer information. It is now based on the 'left-joined query (qryOrdersSource) that includes the Customer and Orders fields. Yes, these tables are joined by the "CustomerID".

My subform within the Main Orders form is called "Orders Detail Subform". It is based on the "Orders Details" table (not a query). This table contains the fields; productid, quantity, unitprice, discount, orderid. It displays the dropdown list with products to choose, shows the quantity selected, discount, unitprice and extended price.
Unfortunately when one query is referring to a particular table, it will not be accessible by another query or form. So what is happening with your situation (my reading of it at the moment, anyway) is that when you open your main form query, it is accessing and thereby 'locking' the table that your subform is based on... Until you release the lock, you cannot make some (or maybe any) changes to it's data from outside of the query that is accessing it.

One solution, as you guessed above, is that your main form be used to display ONLY customer information, and your subform ONLY order information. This will allow you to isolate the two sources and should alleviate the problem of the write conflict you are experiencing with prob #2, and the related problem #1.

If you must refer to customer information on the orders subform, there are some ways we can explore to work around that, but I think you should be able to design the thing so you don't have to.

Let me know how it turns out.

Regards,
Scott
Aug 28 '07 #9
imrosie
222 100+
Unfortunately when one query is referring to a particular table, it will not be accessible by another query or form. So what is happening with your situation (my reading of it at the moment, anyway) is that when you open your main form query, it is accessing and thereby 'locking' the table that your subform is based on... Until you release the lock, you cannot make some (or maybe any) changes to it's data from outside of the query that is accessing it.

One solution, as you guessed above, is that your main form be used to display ONLY customer information, and your subform ONLY order information. This will allow you to isolate the two sources and should alleviate the problem of the write conflict you are experiencing with prob #2, and the related problem #1.

If you must refer to customer information on the orders subform, there are some ways we can explore to work around that, but I think you should be able to design the thing so you don't have to.

Let me know how it turns out.

Regards,
Scott
Scott, hi

I'll post back when I get things scrambled around on this main form. thanks for your insight
Rosie
Aug 28 '07 #10

Post your reply

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

Similar topics

2 posts views Thread by Bryan Feeney | last post: by
9 posts views Thread by nsj | last post: by
5 posts views Thread by jeff | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.