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!

Null values that need to be blank not zero???

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 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 think you might want to adjust your form or report to handle Nulls. To do this, before calculating the value in the form or report, check to see if a value is there. Something like

=IIF(Nz([Qty],0)<>0,Calculate,&quot;&quot;)

This will only display a total if there is one to display, otherwise, it will be blank.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top