divide by 0

[pyromaniac]

This is what I got but it gives #error
Expr1: IIf([query1]![field1]=0,0,[query2]![field2]/[query1]![field1])


It works without the IIF except for the need to catch the DIV/0 error
[query2]![field2]/[query1]![field1]

I also tried
Expr1: IIf([query1]![field1]<>0,[query2]![field2]/[query1]![field1],0)

The division works but seems like the IIF doesn't. Any ideas?

 

[jdraw]

Where do you use this code?Can you show us all of the code?

 

[plog]

Are both fields numbers? Are both fields non-null?

 

[pyromaniac]

I'm not sure about the null or zero. I'm not sure how to test it. They "SHOULDN'T BE" since I use them since I can see the results in another query that concatenates them into a string. Also, I am assuming they are numbers since the division works when not in an iif statement. Maybe I should type cast them to be sure if that is possible in Access.

Here is the SQL. I have been in the GUI so it's probably ugly.

SELECT listLD.MDS, [countType Query].CountOfMDS 

AS [Total Lessons], addCurrent.percOfCurent AS [Current %], ConvertedPercent.PercentYes AS [HTML %], [contLDWith0]+" / "+[contCBIWith0] AS [LD/CBI], [listCBI]![contCBIWith0]/[listLD]![contLDWith0] AS Expr1

FROM (((listLD LEFT JOIN listCBI ON listLD.MDS = listCBI.MDS) LEFT JOIN [countType Query] ON listLD.MDS = [countType Query].MDS) LEFT JOIN addCurrent ON listLD.MDS = addCurrent.MDS) INNER JOIN ConvertedPercent ON listLD.MDS = ConvertedPercent.MDS

ORDER BY listLD.MDS;

***EDIT***attached image of what I am trying to do***

 

[Brianwarnock]

Remember that both the True and False parts of an IIF are always tested, thus an IIF Cannot be used to do this. A simple user function can as you can separate the requirements.

Brian

 

[plog]

First, don't use non-alphanumeric characters for field names. [Current %] should have neither a space nor a percent sign. It just makes life easier down the line.

Second, I'm not really following your JOINS, but I do see a few LEFT JOINS in there. Those cause NULLS. My advice is to pull all the fields that are used in division calculations into the SELECT clause, run the query, sort those fields ascending and see if any NULLs or text values come up. Then you'll know if reality matches "Shouldn't Be".

 

[pyromaniac]

-Brianwarnock
I did not know that. Can I do a traditional if/then statement like in other languages? All I have seen are IIF.

-plog
off to meeting. Will try your suggestion when done.
Thanks

 

[pyromaniac]

I decided to start in its own query so there would be less clutter. I also added new image showing nothing is null. The query that feeds this one does a Nz on the data encase that causes issues.


New SQL

SELECT listLD.MDS, listLD.contLDWith0, listCBI.contCBIWith0, IIf([contLDWith0]>0,0,11111) AS Expr1
FROM listLD LEFT JOIN listCBI ON listLD.MDS = listCBI.MDS;

The point of not having the division in the false spot is I was trying to isolate the issue.

 

[plog]

There you go. Both fields are text.

Further with a LEFT JOIN contCBIWith0 can be NULL.

 

[pyromaniac]

How do you know they are text and can they be type cast as a number?

As for the can be null, I have them fed with 2 queries that are built off the same MDS list and Nz'ed to add 0's in place of nulls. Is this not a good solution?

 

[plog]

The alignment of the values in your screenshot lets me know. Numbers and dates go to the right, text to the left.

A LEFT JOIN includes all records from one table (the FROM table) and just matching records in the other (LEFT JOIN table). So if there's a record in the FROM table, but no match in the LEFT JOIN table, the record will come through in the query but the value you want from the LEFT JOIN table will be NULL.

If ListID and ListCBI are queries, then edit them to cast your numeric values to numbers. This link has a list of functions that does that:

http://www.techonthenet.com/access/functions/

You can cast to Long Int (Clng), double (Cdbl) or a bunch of others.

 

[pyromaniac]

Awesome thanks.

I noticed the alignment difference after trying to figure out what you said and dug a bit. It was throwing me off because I got the data from a count which I assumed would be a number. It was. When I did the Nz, it changed it from number to text and I never noticed. Now i figure i will cast it as a number or I think I read somewhere to use iif instead of Nz so I will look into that route.

Thanks

 

[pyromaniac]

Sweet, it's working now!

Still like sticking in Photoshop but this is getting less scary.