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?
12 1857 @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.
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.
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.
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]));
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?
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?
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.
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?
@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 -
=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.
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
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...
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)!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: WindAndWaves |
last post by:
Can anyone tell me what is wrong with the goto command. I noticed it is one
of those NEVER USE.
I can understand that it may lead to confusing code, but I often use it like
this:
is this...
|
by: Daniel Rudy |
last post by:
What is wrong with this program? When I try to compile it, I get the
following error. Compiler is gcc on FreeBSD.
strata:/home/dcrudy/c 1055 $$$ ->cc -g -oe6-3 e6-3.c
e6-3.c: In function...
|
by: Brian Basquille |
last post by:
Hello all,
Bit of a change of pace now. As opposed to the typical questions regarding
my Air Hockey game, am also working on a Photo Album which uses an Access
Database to store information...
|
by: noridotjabi |
last post by:
Okay. I'm quite embarased to be asking this but I cannot seem to get
files to work. I don't know what the problem is. Is there something
wrong with this: (file related)
#include <stdio.h>...
|
by: Xah Lee |
last post by:
in March, i posted a essay “What is Expressiveness in a Computer
Language”, archived at:
http://xahlee.org/perl-python/what_is_expresiveness.html
I was informed then that there is a academic...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |