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

What's wrong with my IIF statement:

100+
P: 166
Hello:

I am trying to figure out the correct syntax for an IF statement because mine produces incorrect results no matter the placement of the true or false arguments.

Here is the syntax:
iif ( condition, value_if_true, value_if_false )

Here is what I have:
=IIf([txtDiffMatWat]=[TotBW],"Good To Go","There's an Error")

currently, the condition should be true, which should produce "good to go", however, it produces, "there is an error". I changed them around, the true and false parts, and then good to go showed up. I opened the form and then it said, there is an error. Is my syntax correct? If so, why might it be giving the wrong value for the condition?
Jan 29 '09 #1
Share this Question
Share on Google+
12 Replies


beacon
100+
P: 579
@csolomon
What are the values of txtDiffMatWat and TotBW? In your expression you are asking if txtDiffMatWat is equal to TotBW. Make sure that's really what's going on before you change your expression.

If txtDiffMatWat = 3 and TotBW = 4
then your expression will be false

If txtDiffMatWat = 5 and TotBW = 5
then your expression should evaluate to true.

If these conditions are met and it's still not working, post back and include your test values.
Jan 29 '09 #2

100+
P: 166
In the case I am asking about, both controls = 3115, which would evaluate to true.

Initially, I had it set up like the format I posted, but when it gave the wrong choice, I switched the false and true part to this:
=IIf([txtDiffMatWat]=[TotBW],"There's an Error","Good To Go")
SHOULD BE TO MATCH FORMAT:
=IIf([txtDiffMatWat]=[TotBW],"Good To Go","There's an Error")

Which shows Good to Go when both are equal. This goes against what the format says it should be.
Jan 29 '09 #3

beacon
100+
P: 579
These may be stupid questions, but you're using this expression in a query, right? Are both the fields you refer to in the expression also in the query?

Also, are the fields the same format/data type? I don't know if this would make a difference for all numbers or all letters, but I know your results would be screwy if you were trying to match text to number, so maybe matching a long integer with a byte would do the same thing.

I tried your expression out with two date fields and it worked just fine for me...I'd check your fields on your table to make sure everything jives together.
Jan 29 '09 #4

100+
P: 166
The expression is on a form. The data types of the bound fields are Number, Double.
The controls are calculated fields from a query.

here is the query:

SELECT Q_ValidCorrections.DM_Mix, Sum(Q_ValidCorrections.matBatchWeight) AS TotBW, Sum(Q_ValidCorrections.Eight) AS CorrBW, Sum(Q_ValidCorrections.Ten) AS TotWater
FROM Q_ValidCorrections
GROUP BY Q_ValidCorrections.DM_Mix
HAVING (((Q_ValidCorrections.DM_Mix)=[Forms]![T_MCorrections]![DM_Mix]));
Jan 29 '09 #5

Expert 100+
P: 1,287
What exactly is the control source for each of the controls you are trying to compare? Can you see that the controls have the value you want in them when you open the form?
Jan 29 '09 #6

beacon
100+
P: 579
Chip makes a good point. Were you actually typing 3115 into the controls?

Is it possible that the values aren't equal because the decimal values are off? I know that when I told you I tested your expression out with a date/time field, it worked when I entered the value manually, but didn't work when I set the value equal to the system date because there was a second difference.

If you put the expression in the query that is used as the record source for the form, does that return the correct value?
Jan 29 '09 #7

100+
P: 166
Hi guys,

No, the fields are calculated fields. Both are whole numbers so the decimal part would be .00.

My query that is the record source, has a source that is a query and not a table. Here is the query that my query is based off:
SELECT MCorrections.DM_Mix, Material.material, Max(MCTests.corrDt) AS MaxOfcorrDt, MCTests.corrValid, MCTests.corrDryGs, MCTests.corrWetGs, MCTests.corrAbsorption, MixDesign.DM_SampleNo, MixSample.matBatchWeight, [corrWetGs]-[corrDryGs] AS Three, ([corrWetGs]-[corrDryGs])/([corrDryGs]/100) AS Four, ((([corrWetGs]-[corrDryGs])/([corrDryGs]/100))-[corrAbsorption]) AS Six, 100-((([corrWetGs]-[corrDryGs])/([corrDryGs]/100))-[corrAbsorption]) AS Seven, ((((100-(100-((([corrWetGs]-[corrDryGs])/([corrDryGs]/100))-[corrAbsorption]))))*[matBatchWeight])/100)+[matBatchWeight] AS Eight, ((((100-(100-((([corrWetGs]-[corrDryGs])/([corrDryGs]/100))-[corrAbsorption]))))*[matBatchWeight])/100)/8.33 AS Nine, (((((100-(100-((([corrWetGs]-[corrDryGs])/([corrDryGs]/100))-[corrAbsorption]))))*[matBatchWeight])/100)/8.33)*8.33 AS Ten
FROM (MixDesign INNER JOIN ((MCorrections INNER JOIN MCTests ON MCorrections.correctionsID = MCTests.correctionsID) INNER JOIN MixSampleCorrections ON MCorrections.correctionsID = MixSampleCorrections.correctionsID) ON MixDesign.DM_Mix = MCorrections.DM_Mix) INNER JOIN (Material INNER JOIN MixSample ON Material.materialID = MixSample.materialID) ON (MixSample.DM_MaterialNo = MixSampleCorrections.DM_MaterialNo) AND (MixDesign.DM_Mix = MixSample.DM_Mix)
GROUP BY MCorrections.DM_Mix, Material.material, MCTests.corrValid, MCTests.corrDryGs, MCTests.corrWetGs, MCTests.corrAbsorption, MixDesign.DM_SampleNo, MixSample.matBatchWeight, [corrWetGs]-[corrDryGs], ([corrWetGs]-[corrDryGs])/([corrDryGs]/100), ((([corrWetGs]-[corrDryGs])/([corrDryGs]/100))-[corrAbsorption]), 100-((([corrWetGs]-[corrDryGs])/([corrDryGs]/100))-[corrAbsorption]), ((((100-(100-((([corrWetGs]-[corrDryGs])/([corrDryGs]/100))-[corrAbsorption]))))*[matBatchWeight])/100)+[matBatchWeight], ((((100-(100-((([corrWetGs]-[corrDryGs])/([corrDryGs]/100))-[corrAbsorption]))))*[matBatchWeight])/100)/8.33, (((((100-(100-((([corrWetGs]-[corrDryGs])/([corrDryGs]/100))-[corrAbsorption]))))*[matBatchWeight])/100)/8.33)*8.33
HAVING (((MCorrections.DM_Mix)=[Forms]![T_MCorrections]![DM_Mix]) AND ((MCTests.corrValid)=True));


I just use the calculated fields: Ten and Eight in the query that is the source query.
Jan 29 '09 #8

Expert 100+
P: 1,287
You didn't actually answer either of my questions.
On the control's property sheet, data tab, what does the Control Source say? If it's unbound, how do you set it in the code?
When these controls are visible on your form, can you see that they have the correct values in them?
Jan 29 '09 #9

DonRayner
Expert 100+
P: 489
@csolomon
Just because your textbox controls are displaying a value of 3115 doesn't mean that the underlaying data is 3115.

3115.0002345 does not equal 3115, Try changing your expression to something like

Expand|Select|Wrap|Line Numbers
  1. =IIf(FormatNumber([txtDiffMatWat],0) = FormatNumber([TotBW],0),"Good To Go","There's an Error")
  2.  
You can change the 0 to whatever number of decimal places you want.
Jan 29 '09 #10

Expert 100+
P: 634
Hi

Just my tuppence worth, but when comparing two 'Real' nubers A & B (as opposed to integers) for equality, I use this expression
Expand|Select|Wrap|Line Numbers
  1. IF Abs(A-B)<0.0001 Then 
  2.     "Equal"
  3. else
  4.     "Not Equal"
  5.  end if
You can adjust the >0.0001 to any tollerance figure you feel comfotable with for the application.

HTH

MTB
Jan 30 '09 #11

Expert Mod 2.5K+
P: 2,545
Hi all. When dealing with floating-point values it is inappropriate to test for equality, because as Don has indicated rounding errors mean that values are rarely stored in an exact way. This is inherent in the limitations on the precision of floating-point decimal numbers represented in single- or double-precision variables in binary; in some cases it is a limitation of the number system itself.

For example, numbers such as 1/3 have no exact decimal representation, being represented approximately as 0.333333...333 to a finite number of decimal places. When multiplied by 3 the result is 0.999999...99, a value that will not match for equality with 1.0.

To overcome the inherent rounding difficulties of floating-point numbers don't test them for equality; instead, test for the absolute value of the difference being less than some threshold. Choose the threshold so that it is lower than any likely increment of the numbers to avoid false positives.

Example:

Expand|Select|Wrap|Line Numbers
  1. IIF(Abs([f1]-[f2])<0.0001, "Close enough to equality", "Not considered equal")
Threshold of 0.0001 would be OK for a two-decimal place value. In general, I'd suggest a threshold at least two orders of magnitude lower than the lowest increment value likely to be encountered.

For interest, the rounding errors in Excel are very small, at around 10^-14, but still significant in the context of testing for exact equality in floating point numbers. I tend to set 'equality' thresholds at around 10^-11 when working in Excel, but it uses greater precision in the representation of numbers than double-precision values in VBA do.

-Stewart

PS apologies to MTB - your post and mine crossed. Uncanny...
Jan 30 '09 #12

100+
P: 166
Thank you for the many responses.

I decided to use this one:

=IIf(FormatNumber([txtDiffMatWat],0) = FormatNumber([TotBW],0),"Good To Go","There's
an Error")

since it was the shortest, and i understood it more.

Thanks guys (and gals)!
Feb 3 '09 #13

Post your reply

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