What's wrong with my IIF statement:

[csolomon]

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?

[beacon]

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?

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.

[csolomon]

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.

[beacon]

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.

[csolomon]

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_ValidCorrecti ons.DM_Mix, Sum(Q_ValidCorr ections.matBatc hWeight) AS TotBW, Sum(Q_ValidCorr ections.Eight) AS CorrBW, Sum(Q_ValidCorr ections.Ten) AS TotWater
FROM Q_ValidCorrecti ons
GROUP BY Q_ValidCorrecti ons.DM_Mix
HAVING (((Q_ValidCorre ctions.DM_Mix)=[Forms]![T_MCorrections]![DM_Mix]));

[ChipR]

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?

[beacon]

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?

[csolomon]

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.materi al, Max(MCTests.cor rDt) AS MaxOfcorrDt, MCTests.corrVal id, MCTests.corrDry Gs, MCTests.corrWet Gs, MCTests.corrAbs orption, MixDesign.DM_Sa mpleNo, MixSample.matBa tchWeight, [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.co rrectionsID = MCTests.correct ionsID) INNER JOIN MixSampleCorrec tions ON MCorrections.co rrectionsID = MixSampleCorrec tions.correctio nsID) ON MixDesign.DM_Mi x = MCorrections.DM _Mix) INNER JOIN (Material INNER JOIN MixSample ON Material.materi alID = MixSample.mater ialID) ON (MixSample.DM_M aterialNo = MixSampleCorrec tions.DM_Materi alNo) AND (MixDesign.DM_M ix = MixSample.DM_Mi x)
GROUP BY MCorrections.DM _Mix, Material.materi al, MCTests.corrVal id, MCTests.corrDry Gs, MCTests.corrWet Gs, MCTests.corrAbs orption, MixDesign.DM_Sa mpleNo, MixSample.matBa tchWeight, [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.corrV alid)=True));


I just use the calculated fields: Ten and Eight in the query that is the source query.

[ChipR]

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?

[DonRayner]

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

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

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

You can change the 0 to whatever number of decimal places you want.

[MikeTheBike]

Hi

Just my tuppence worth, but when comparing two 'Real' nubers A & B (as opposed to integers) for equality, I use this expression

IF Abs(A-B)<0.0001 Then 
    "Equal"
else
    "Not Equal"
 end if

You can adjust the >0.0001 to any tollerance figure you feel comfotable with for the application.

HTH

MTB

[Stewart Ross]

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:

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...

[csolomon]

Thank you for the many responses.

I decided to use this one:

=IIf(FormatNumb er([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)!