Hi there... I have an invoice that

[BCre8iv]

Hi there...
I have an invoice that is printed off daily. The invoice is based on the query below. Now if the client actually has an preorder and this information is filled out in the order form, my invoice prints out fine. My problem is, most of the time a blank invoice with only customers address and routes on the invoice r needed. The driver then fills in the rest of the information. So what they do is open up the orders form pick the customer but leave the order detail blank. How do I get my query to bring in both groups of customers, the ones with the pre-ordered info and the blank ones so that the invoice report can be printed in order of route in one group?

Here is what I have...this gives me pre-ordered customers only. Any help would be gr8. Thx. BC
SELECT [Orders Qry].OrderID, [Orders Qry].CustomerID, [Orders Qry].EmployeeID, [Orders Qry].OrderDate, [Orders Qry].RequiredDate, [Orders Qry].ShippedDate, [Orders Qry].ShipVia, [Orders Qry].[Tax Total], [Orders Qry].Freight, [Orders Qry].ShipName, [Orders Qry].ShipAddress, [Orders Qry].ShipCity, [Orders Qry].ShipRegion, [Orders Qry].ShipPostalCode, [Orders Qry].ShipCountry, [Orders Qry].Phone, [Orders Qry].Cell, [Orders Qry].CompanyName, [Orders Qry].Address, [Orders Qry].City, [Orders Qry].Region, [Orders Qry].PostalCode, [Orders Qry].Country, [Orders Qry].Route, [Orders Qry].RoutePosition, [Orders Qry].[Delivery Notes], [Order Details Extended].ProductID, [Order Details Extended].ProductName, [Order Details Extended].UnitPrice, [Order Details Extended].Quantity, [Order Details Extended].Returns, [Order Details Extended].[Tax Rate], [Order Details Extended].ExtendedPrice, [Order Details Extended].TaxAmount
FROM [Order Details Extended] INNER JOIN [Orders Qry] ON [Order Details Extended].OrderID = [Orders Qry].OrderID
GROUP BY [Orders Qry].OrderID, [Orders Qry].CustomerID, [Orders Qry].EmployeeID, [Orders Qry].OrderDate, [Orders Qry].RequiredDate, [Orders Qry].ShippedDate, [Orders Qry].ShipVia, [Orders Qry].[Tax Total], [Orders Qry].Freight, [Orders Qry].ShipName, [Orders Qry].ShipAddress, [Orders Qry].ShipCity, [Orders Qry].ShipRegion, [Orders Qry].ShipPostalCode, [Orders Qry].ShipCountry, [Orders Qry].Phone, [Orders Qry].Cell, [Orders Qry].CompanyName, [Orders Qry].Address, [Orders Qry].City, [Orders Qry].Region, [Orders Qry].PostalCode, [Orders Qry].Country, [Orders Qry].Route, [Orders Qry].RoutePosition, [Orders Qry].[Delivery Notes], [Order Details Extended].ProductID, [Order Details Extended].ProductName, [Order Details Extended].UnitPrice, [Order Details Extended].Quantity, [Order Details Extended].Returns, [Order Details Extended].[Tax Rate], [Order Details Extended].ExtendedPrice, [Order Details Extended].TaxAmount
ORDER BY [Orders Qry].Route, [Orders Qry].RoutePosition;

Thx again...BC

 

[RickSpr]

First, get rid of the GROUP BY clause, or equivalently, unclick the Summation button (greek letter sigma) on the toolbar. You're not using any column aggregate functions, so it doesn't do anything in this query.

Next, change your INNER JOIN into a RIGHT JOIN. That will give you the [Orders Qry] rows that have no details.

Be aware that, for orders with no details, the [Order Details Extended] fields will be Null. That may cause some trouble on your Invoice report. If so, try changing "[Order Details Extended].[field name]" to "Nz([Order Details Extended].[field name])". Rick Sprague
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[BCre8iv]

Okay, Rick I did the first two things. Thx
I also tried the Nz([Order Details Extended].[field name]) but my report now comes up with a Enter parameter value box. Not good. Just so you know, nulls and I don't get along very well.
And without the Nz blurp My report doesn't open at all, I get "Invalid use of Nulls".
If u fill in a parameter all invoices come up with that info.
Tina