Hi there...I forgot to give this a title the other day, sorry.
I have an invoice that is printed off daily. The invoice is based on the query below. Now if the client actually has a 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 on site, therefore the fields from the Order Details Extended need to remain blank. As well the calculated fields, IE Totals, which base the calculations on Unit Price, Qty, etc, also need to be blank, not filled in with "Error". Any suggestions on how to make all this happen?
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;
Now I tried a Right Join, but all I got was an error "Improper use of Nulls".
Thx again...BC
I have an invoice that is printed off daily. The invoice is based on the query below. Now if the client actually has a 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 on site, therefore the fields from the Order Details Extended need to remain blank. As well the calculated fields, IE Totals, which base the calculations on Unit Price, Qty, etc, also need to be blank, not filled in with "Error". Any suggestions on how to make all this happen?
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;
Now I tried a Right Join, but all I got was an error "Improper use of Nulls".
Thx again...BC