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!

IIf(IsNull) query producing unwanted parameter in report

Status
Not open for further replies.

BCre8iv

Programmer
May 21, 2002
28
CA
Hi, I have a report that is based on a query where I have used IIf(IsNull). When I look at the query it is giving me the records that I want, however when I open the report that is based on the query I get a parameter dialog box. If I click OK to close the box and open the report, the report comes up with all the fields blank, if I enter a paramter all records end up with the info that I have inputted. Niether result is what I want. Any help would be gr8? Thx
Tina
 
What is your whole IIF statement. Also, do you calculate any values in the query.

Paul
 
This is the entire query. And Extended detail and tax amount fields are based on calculated fields from the Order Detail Query.
QrySELECT [Orders Qry].OrderID, [Orders Qry].CustomerID, [Orders Qry].OrderDate, [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], IIf(IsNull([ProductID])," ",[ProductID]) AS Product, IIf(IsNull([ProductName])," ",[ProductName]) AS Name, IIf(IsNull([UnitPrice])," ",[UnitPrice]) AS Price, IIf(IsNull([Quantity])," ",[Quantity]) AS Qty, IIf(IsNull([Returns])," ",[Returns]) AS Rtns, IIf(IsNull([Tax Rate])," ",[Tax Rate]) AS Rate, IIf(IsNull([ExtendedPrice])," ",[ExtendedPrice]) AS Extended, IIf(IsNull([TaxAmount])," ",[TaxAmount]) AS Tax, [Orders Qry].PaymentsperBottle, [Orders Qry].Renter, [Orders Qry].Leasee
FROM [Order Details Extended] RIGHT JOIN [Orders Qry] ON [Order Details Extended].OrderID = [Orders Qry].OrderID
ORDER BY [Orders Qry].Route, [Orders Qry].RoutePosition;

Thx Tina
PS I need to have this solved by 10am on the 14th.
 
Sorry I should have asked right out what the parameter box was requsting when it ran
10am whose time..[sleeping2]


Paul
 
I figured out the parameter box thing. Stupid mistake, had the wrong fields on the report. Now I just need to solve the calculated fields on the reports when dealing with nulls.
My time and that is in about 13 hrs or so.
Thx
Tina
 
You don't say too much about the calculated fields but you can try the Nz() function.
Nz([Extended Price],0)
returns 0 if Extended Price is Null.
It really depends on what the calculations are. If you need to divide by a value that might be null then you would have to trap the Overflow error like this

IIf(IsNull([ExtendedPrice]),0,[SomeField]/[ExtendedPrice])

One of these may help. If not post back what the issues are and we'll see what we can do.

Paul
 
Sorry for the lack of detail.This is what I have for the Extended price:
Extended: IIf(IsNull([Price]*[Qty])," ",[Price]*[Qty])

This is for the Tax Amount:
Tax: IIf(IsNull([Order Details].[UnitPrice]*[Quantity]*(1/100*[Rate])*100)," ",([Order Details].[UnitPrice]*[Quantity]*(1/100*[Rate])*100))

I can't have zeros show up, it has to be blank if all the other fields are empty.
Thx Tina
 
In your query don't test for Nulls. Just do the calculations for [Price] * [Qty] and the other one. I think if one or both of the fields is null the answer will be null.


Paul
 
Sorry the tax field reads:
Tax: IIf(IsNull([Price]*[Qty]*(1/100*[Rate])*100)," ",([Price]*[Qty]*(1/100*[Rate])*100))

Thx again
Tina
 
Okay this is what I put: Extended: ([Price]*[Qty])
and I still get an #error in the field.
Tina
 
Sorry, the site has been down and it's been a busy morning for me. What I did was take two numeric fields from a table, left some values blank and then performed some calculations on them in a query. Whenever there was a null value in a field, it returned a Null value in the Calculation. The problem is if you test for Null with the IsNull() function then numeric values get all screwed around, as you've probably found out. Also, you can't test for multiple fields within one IsNull() function like
IsNull(Qty * Price). It has to be
IIf(IsNull(Qty) Or IsNull(Price), " ", Qty * Price)
But this expression won't evaluate to a Null answer because you are using numeric data types. One work around is to use do this. In the IIF statement set the argument to 0 instead of " "
TotalCost:IIf(IsNull(Qty) Or IsNull(Price), 0, Qty * Price)

Then in the format event in your Report put code like this

If Me.TotalCost = 0 Then
Me.Totalcost.Visible = False
Else
Me.TotalCost.Visible = True
End If

This will create blanks in your report where the TotalCost is 0.

Sorry, I'm not sure why the query returns error when something is null. It' worked fine in all the tests I ran.

Paul
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top