Access Problem

[BigMike80]

Can anyone Help I am trying to use the expression builder to create an expression that returns a zero when a formula tries to divide by zero.
So far my attempts have failed.
This is what I have tried so far. I feel my logic is correct maybe my syntax is off.
Expr1: IIf(Bsheetcostdraft![CSA/SAA]=0,0,Icostcostdraft![CSA/SAA]/Bsheetcostdraft![CSA/SAA])
Can anyone guide me through this.

[Rabbit]

Have you tried '.' instead of '!'? Also I think you made a typo with lcostcostdraft, should probably be lsheetcostdraft?

[BigMike80]

Have you tried '.' instead of '!'? Also I think you made a typo with lcostcostdraft, should probably be lsheetcostdraft?

I did try switching the "!" for "." To no avail and I know my naming conventions seem wierd but they are correct. I really stranded here. Can you even put an expression like this in the space marked field for a select query.

[Rabbit]

Yes, you can do this. What about the fields with '/' Have you tried removing those characters from the field name? This may impact other queries you have.

[BigMike80]

No Dice! Still does not work. This problem does not make any sense to me. I have two tables that hold similar information one comes from the blue sheet the other from Icost. We use two costing tools to provide information. This database holds the info then runs this query to see what the difference is pct wise between the two tools. I just designed it last week.

[Denburt]

I think the issue is just proper coding methods.
1. Is the following reffering to controls on a form or subform and if yes which is it a form or subform?
2. Is this code in the Main form or elsewhere?

If we are dealing with code in a form name "Bsheetcostdraft", It should read more like this:

IIf(Me!Bsheetcostdraft![CSA/SAA]=0,0,Me!Bsheetcostdraft![CSA/SAA]/Me!Bsheetcostdraft![CSA/SAA])
 

When I look at what you have I am just lost since I can not tell and have no idea what the following names are used for (subform maybe?).
lsheetcostdraft
or
Bsheetcostdraft

[ADezii]

Can anyone Help I am trying to use the expression builder to create an expression that returns a zero when a formula tries to divide by zero.
So far my attempts have failed.
This is what I have tried so far. I feel my logic is correct maybe my syntax is off.
Expr1: IIf(Bsheetcostdraft![CSA/SAA]=0,0,Icostcostdraft![CSA/SAA]/Bsheetcostdraft![CSA/SAA])
Can anyone guide me through this.

Never, Never, Never use the IIF() Function whenever there is even a remote possibility that a division by 0 could occur anywhere within the Expression. This Function will evaluate both the truepart and falsepart whether or not the truepart evaluates to True. This is 1 of the hugh pitfalls with the IIF() Function that was previously pointed out in a TheScripts Tip of the Week. A simple illustration will demonstrate this point:

dblNew = IIf(intY = 0, 0, intX/intY)

'This would seem harmless since you apparently covered your bases in the case where intY = 0 and you assumed the result would be 0. Unfortunately, if intY = 0, this statement will still cause a Runtime Error since the 2nd portion of the IIF expression will also be evaluated thus triggering a Division by 0 Error. Use the If...Then...Else statement in cases such as this NOT IIf(). Hope this helps.

[Rabbit]

Never, Never, Never use the IIF() Function whenever there is even a remote possibility that a division by 0 could occur anywhere within the Expression. This Function will evaluate both the truepart and falsepart whether or not the truepart evaluates to True. This is 1 of the hugh pitfalls with the IIF() Function that was previously pointed out in a TheScripts Tip of the Week. A simple illustration will demonstrate this point:

dblNew = IIf(intY = 0, 0, intX/intY)

'This would seem harmless since you apparently covered your bases in the case where intY = 0 and you assumed the result would be 0. Unfortunately, if intY = 0, this statement will still cause a Runtime Error since the 2nd portion of the IIF expression will also be evaluated thus triggering a Division by 0 Error. Use the If...Then...Else statement in cases such as this NOT IIf(). Hope this helps.

I tested this and I didn't get a runtime error. Works just fine.

[BigMike80]

I tested this and I didn't get a runtime error. Works just fine.

Just so everyone is aware I am not running vba code this is part of a select query that I am trying to run.

[ADezii]

I tested this and I didn't get a runtime error. Works just fine.

It is only within the context of VBA Code, not SQL, that the Division by 0 - Error 11 will occur. I tested it several times and it does, in fact, generate this Error.

[Denburt]

If your looking at a query in design view your select query statement should read:

MyNewFieldName:iif(tablename1.fieldnameofTable1=0,0,tablename1.fieldnameofTable1/tablename2.fieldnameofTable2)

ADezii I just wanted to say that for VBA purposes you are absolutely correct also it is much slower than the if then statement since it has to evaluate both parts.

[BigMike80]

If your looking at a query in design view your select query statement should read:

MyNewFieldName:iif(tablename1.fieldnameofTable1=0,0,tablename1.fieldnameofTable1/tablename2.fieldnameofTable2)

ADezii I just wanted to say that for VBA purposes you are absolutely correct also it is much slower than the if then statement since it has to evaluate both parts.

Yes I realize this but I am starting to think there is another problem let me explain. I am getting error message even when I am not dividing by zero. When I apply that formula. However when I take out the iif statement and just run it as simple division the formula will work unless I divide by zero. I think I am missing something more. Is there a possibility that this is a property error. I have all my fields set to except text. I dont know what else could be the problem.

[Denburt]

fields set to except text

If the fields in question are not numeric fields then that could be your problem.

[BigMike80]

Guys the problem is solved I was an idiot and specified every field as text when it should have been number.

[ADezii]

If your looking at a query in design view your select query statement should read:

MyNewFieldName:iif(tablename1.fieldnameofTable1=0,0,tablename1.fieldnameofTable1/tablename2.fieldnameofTable2)

ADezii I just wanted to say that for VBA purposes you are absolutely correct also it is much slower than the if then statement since it has to evaluate both parts.

BTW, the same type problems arise when dealing with the Choose() and Switch() Functions within the same context.

[Denburt]

BTW, the same type problems arise when dealing with the Choose() and Switch() Functions within the same context.

I will remember this...

Glad you got it Mike

[Rabbit]

BTW, the same type problems arise when dealing with the Choose() and Switch() Functions within the same context.

I only tested in SQL as that is the only place I would use iif().

[Denburt]

I only tested in SQL as that is the only place I would use iif().

Did you see the article I posted earlier about using it in a query?
iif() and SQL

I posted more in this article than I wanted to so if someone wouldn't mind cleaning some of it out I just wanted the portion about the iif() statement...