Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Invoice that includes Null values?

Status
Not open for further replies.

BCre8iv

Programmer
May 21, 2002
28
CA
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 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
 
Hi

You need to do a Right join (to include all from Orders), and (probably, not sure without knowing the exact details of your invoice print), some NZ()'s around your quantities, values etc

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Hi
I have the right join in place and I get invalid use of nulls, which i expected. I'm not very good with dealing with Nulls, getting better though. Neway, doesn't NZ()'s put a zero in the field? I need the quantities, values, etc to remain blank so that the drivers can write in the info on the invoice on location?
Thx
BC
 
Hi

Nz() will put whatver you want syntax is:

Nz(Varname,value)

so you could have

Nz(MyVAlue,0)

or

Nz(MyValue," ")

or

Nz(MyValue,"-")

etc Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top