IIf(IsNull) query producing unwanted parameter in report

[BCre8iv]

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

 

[PaulBricker]

What is your whole IIF statement. Also, do you calculate any values in the query.

Paul

 

[BCre8iv]

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.

 

[PaulBricker]

Sorry I should have asked right out what the parameter box was requsting when it ran
10am whose time..


Paul

 

[BCre8iv]

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

 

[PaulBricker]

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

 

[BCre8iv]

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

 

[PaulBricker]

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

 

[BCre8iv]

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

Thx again
Tina

 

[BCre8iv]

Okay this is what I put: Extended: ([Price]*[Qty])
and I still get an #error in the field.
Tina

 

[PaulBricker]

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