Testing a query

[c918768]

I'm having trouble debugging a query that doesn't seem to be returning a correct result. I've simplified the query just to test it but when I click Run I don't see any information regarding whether it ran successfully. Can I run it from this window or does it need to executed in another manner? I attached a screen shot.

[Gustav]

Try with: Select * From tblFTA_Final

[madpiet]

open the query in design mode, click the SQL button and then post that here. Can't fix what we can't see.

[Welshgasman]

You need a FROM clause?

[madpiet]

Oh, stupid me. Forgot how to read. Yeah, a FROM clause helps a lot!! Sometimes JOINs are helpful too!

[c918768]

I've gotten the query to run but I'm getting an error. Its trying to prevent division by zero but it looks like it might be testing non-numeric fields for 0. Can someone suggest what changes I should make?

SELECT tblFTA_Final.*, IIf([Occurances]=0,0,IIf([Outages]=0,0,IIf([population]=0,0,IIf([MaterialFailures]=0,0,([Occurances]/[Outages])/([Population]/[MaterialFailures]))))) AS Expr1
FROM tblFTA_Final;

MaterialFailures	Population	Outages	Occurances	Computation	Expr1
0	369600		0	0	0
1	369600		0	0	0
0	369600		0	0	0
0	369600		0	0	0
0	369600		0	0	0
0	369600		0	0	0
0	369600		1	0	#Error
0	369600		0	0	0
0	369600		0	0	0
0	369600		0	0	0
0	369600		0	0	0
0	369600		0	0	0

[madpiet]

This is asking for trouble:
([Occurances]/[Outages])/([Population]/[MaterialFailures]))))) AS Expr1

If [Outages], [Population] OR [MaterialFailures] is null, you'll get no answer. If any is zero, you'll get a division by zero error. One way to avoid that is to filter out any the records where any one of those is zero. You basically have to filter out any of those records to avoid the divide by zero error.

SELECT *
FROM <source object>
WHERE [Outages] > 0
AND [Population] > 0
AND [Material Failures] > 0

and then you'd base your totals query on that. Otherwise, you're going to generate DIV/0 errors.

[Micron]

IIf([Outages]=0,0

Nor do any of those make sense. In English it says,' if outages is zero then use 0'. If you have to use something like that, why not use 1 as the value for when the expression is false? Then division by 1 won't change the value - 0/1 = 0. But I agree, better to eliminate those records if you can.

[isladogs]

Hi @Gustav
Good to see you here as well

For the benefit of others, Gustav has been an Access MVP since around 2017 and is an expert. He is certainly NOT a novice which for some reason is purely based on post count at this forum!

[Welshgasman]

Hi @Gustav
Good to see you here as well

For the benefit of others, Gustav has been an Access MVP since around 2017 and is an expert. He is certainly NOT a novice which for some reason is purely based on post count at this forum!

Yes, and we have many 'Competent Performer's here, which are really not.

[Micron]

I knew that long ago when I got bumped up from novice to VIP and thought "What?". It's arbitrary for sure, so I never go by the forum ranking when trying to assess anyone's level of competency in Access. I'm still a VIP of a sort though.

[jojowhite]

try:

SELECT tblFTA_Final.*, IIf([Outages]=0 Or [population]=0 Or [MaterialFailures]=0, 0,([Occurances]/[Outages])/([Population]/[MaterialFailures])) AS Expr1
FROM tblFTA_Final;

[June7]

Or

IIf(IsNull(Outages + Population + MaterialFailures), 0, ([Occurances]/[Outages])/([Population]/[MaterialFailures]))

Or

IIf(Outages + Population + MaterialFailures IS NULL, 0, ([Occurances]/[Outages])/([Population]/[MaterialFailures]))


Side Note: correct spelling of Occurances would be Occurrences

[jojowhite]

based on the Query string and the output on post #4, there is no Null values there.

[moke123]

I knew that long ago when I got bumped up from novice to VIP and thought "What?". It's arbitrary for sure, so I never go by the forum ranking when trying to assess anyone's level of competency in Access. I'm still a VIP of a sort though.

You've always been a VIP to those of us who've known you for years.