By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,872 Members | 2,392 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,872 IT Pros & Developers. It's quick & easy.

Access Problem

P: 6
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.
Mar 21 '07 #1
Share this Question
Share on Google+
17 Replies


Rabbit
Expert Mod 10K+
P: 12,364
Have you tried '.' instead of '!'? Also I think you made a typo with lcostcostdraft, should probably be lsheetcostdraft?
Mar 21 '07 #2

P: 6
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.
Mar 21 '07 #3

Rabbit
Expert Mod 10K+
P: 12,364
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.
Mar 21 '07 #4

P: 6
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.
Mar 21 '07 #5

Denburt
Expert 100+
P: 1,356
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:
Expand|Select|Wrap|Line Numbers
  1. IIf(Me!Bsheetcostdraft![CSA/SAA]=0,0,Me!Bsheetcostdraft![CSA/SAA]/Me!Bsheetcostdraft![CSA/SAA])
  2.  
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
Mar 21 '07 #6

ADezii
Expert 5K+
P: 8,628
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:

Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 22 '07 #7

Rabbit
Expert Mod 10K+
P: 12,364
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:

Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 22 '07 #8

P: 6
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.
Mar 22 '07 #9

ADezii
Expert 5K+
P: 8,628
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.
Mar 22 '07 #10

Denburt
Expert 100+
P: 1,356
If your looking at a query in design view your select query statement should read:
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 22 '07 #11

P: 6
If your looking at a query in design view your select query statement should read:
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 22 '07 #12

Denburt
Expert 100+
P: 1,356
fields set to except text
If the fields in question are not numeric fields then that could be your problem.
Mar 22 '07 #13

P: 6
Guys the problem is solved I was an idiot and specified every field as text when it should have been number.
Mar 22 '07 #14

ADezii
Expert 5K+
P: 8,628
If your looking at a query in design view your select query statement should read:
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 22 '07 #15

Denburt
Expert 100+
P: 1,356
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
Mar 22 '07 #16

Rabbit
Expert Mod 10K+
P: 12,364
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().
Mar 23 '07 #17

Denburt
Expert 100+
P: 1,356
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...
Mar 23 '07 #18

Post your reply

Sign in to post your reply or Sign up for a free account.