Problem with divide by zero in linked table?

[Icehousman2]

I need to display a Price per Square feet calculation on a report. The problem is that, the query that calculates this is based off a linked table that can sometimes contain a zero value in the square feet field. The data can be manipulated if need be (although I didn't think it was possible to modify a linked table) but it needs to be automatic so the user doesn't know what is happening. I figured that i would need some type of do until loop in vb to accomplish this, but not sure how exactly to go about it. Any ideas would be greatly appreciated. Thanks in advance.

 

[KenHigg]

Can you try a iif()?

 

[Icehousman2]

Do you mean inside the Do until loop? Basically I need to check every record in the table, to make sure that the square feet field is not zero, and if it is change it to "" so that it won't blow up the query. I just don't know the code that would accomplish this. And where to put it. Here is code that i'm working on:

Dim rst As Recordset
Dim itm As Object
Set dbs = CurrentDb
Set rst = dbs.openrecordset("Comps", dbOpenDynaset)
Do Until rst.EOF
Set itm = fld.TOTALSF
If itm = 0 Then
itm = ""
rst.MoveNext
Else
rst.MoveNext
End If

Loop

I'm just a beginner in vb so I'm not sure if this is how you should set this up. Thanks for the help.

 

[KenHigg]

If you are doing this in a report, then I would think you could just test for zero in the underlying query.

In the query that the report is based on could you try something like the following:

NewField: iif([value_2]=0,0,[value_1]/[value_2])

Make sense?

 

[Icehousman2]

That should work. WOW i was really trying to do it the hard way. I'll give it a try and let you know. THanks for the help.

 

[Icehousman2]

The query is working now, but i'm getting an overflow error when I try and open the report. You wouldn't happen to have an idea on that would you. Thanks again.

 

[Icehousman2]

I should mention that the subreport is based on a query that queries the first query you helped me on. Sorry was that confusing. And this other query is the one that is now having the overflow problem.