Expression Builder Bad Results

[lhooker]

I have a table that has columns with names of Married #1, Married #2, Borrower#1 and Borrower#2. All are text fields. In the report's 'Expression Builder' for the 'Control Source', I have [Borrower#1]. This gives the name of Borrower#1, but when I change it to =[Borrower#1], I get both Borrower#1 and Borrower#2 names. This was found out when the below statement gave both Borrower#1 and Borrower#2 names when I only requested the name of Borrowers#1.

=IIf([Married #1]="Married",([Borrower#1] & "Is Married"))

My expected results is ====> 'John W. Doe Is Married' NOT 'Joe W. Doe & Mary R. Jones Is Married' (when the value of Borrower#2 is Mary R. Jones.

What is causing this ?

Thanks ! ! !

 

[pbaldy]

I've never seen that sort of behavior. Can you post a sample db that does it?

 

[lhooker]

Paul,

I tried to attach a sample.mdb (zipped) with one record and the report, but the size exceeds the limit. Which is surprising. The zipped file is 641 kb. The limitation is 393 kb for zip files. Any ideas ?

Thanks ! ! !

 

[shudini]

pm me i'll give you my email address i'm curious to see this too.

 

[pbaldy]

Make sure you compact it before zipping. It should be small enough then (unless it has embedded images).

 

[lhooker]

Paul,

Mmmmm, that was weird. I had to create a new db and import in the table and report to reduce the size. Anyway the file is attached. Thanks ! ! !

 

[shudini]

Solution is simple.

Take a look at the first text box you have, you named it Borrower#1. This means, when you reference in the second txtBox Borrower#1, you are referencing the text box on your report, not the data field Borrower#1. You probably dragged the field onto the report and access created a bound control, and then you probably edited the text box contents. Just change the name of your first text box to something else, then make sure to fix text box number 2 (when you change the first on, the second one will be updated to reflect the new name of text box #1), and then all should work.

 

[lhooker]

Shuie,

That was it . . . Thanks ! ! !

 

[lhooker]

Expression Builder Results

OK that fixed the immediate problem of the combining of Borrower #1 and Borrower #2 names, but when I added the rest of the if statements (nested if), another problem surfaced. You see anything wrong with these if
statements ?

=IIf([Married #1] And [Married #2]="Married",("Married Couple"),
IIf([Married #1] And [Married #2]="Unmarried",("Both Unmarried"),
IIf([Married #1]="Married",([Borrower#1] & " Is Married"))))

What's happening is that I have Borrower #1 ('Married') and Borrower #2 ('Unmarried'). The results from from the above nested if statement is 'Both Unmarried', which is wrong. The results should be the value of Borrower #1 in the table with 'Married'. Attached is the 'Sample.zip' file. Lastly, where can I find documentation on statement format/allowed statements in the 'Expression Builder'. Thanks ! ! !

 

[shudini]

the dynamics of an iif statement are that you need to provide logical statements, as many as you want, that ultimately result in a boolean (true/false) value. while what you wrote sounded logical in english, the computer can't understand what you mean since you are not passing full logical statements.

IIf([Married #1] And [Married #2]="Married"

what you mean to say is if both married#1 and married #2 are equal to married (true or false) then return "married". that statement is incomplete. what you need is

IIf([Married #1][B]="Married"[/B][COLOR="Red"][B](Statement # 1 evaluates to true or false)[/B][/COLOR] 
And  [Married #2]="Married"[COLOR="Red"][B](Statement # 2 evaluates to true or false)[/B][/COLOR] ,
[COLOR="Red"][B](iif(TRUE And TRUE then...)[/B][/COLOR]"Married Couple"...

The same goes for the other statements.

 

[lhooker]

Shuie,,

I understand what you are saying, but having trouble with the syntax. What is wrong with the below statement ? Is there any documentation available for syntax for 'IIF' and 'And' sttements ?

=IIf([Married #1]="Married" And [Married #2]="Married", (IIf(TRUE And FALSE), ([Borrower#2] & "Unmarried"))))

 

[shudini]

sorry, i wasn't being literal, the red text was just commenting what i was doing. The following is the easiest explanation, although you could go anywhere online to get much more in depth.

iif(statement here must evaluate to true or false, value if true, value if false)

with an IIf() statement, you need to put in the value if True and value if False statements, otherwise it will return an error.

and, or, not are all examples of logical operators, just like +, -, *, / are mathematical operators, just with math you get numbers for answers, and with logical equations you get true or false. 1 +1 = 2, True AND True= True. with the AND operator, all values must result in TRUE, with OR, any value must result in TRUE, and with NOT, all values must result in FALSE. You can use your imagination as to how to mix and match these operators.

for your example, here is what you need:

=IIf([Married #1] [B]="Married"[/B] And [Married #2]="Married",("Married Couple"),
IIf([Married #1] [B]="Unmarried"[/B] And [Married #2]="Unmarried",("Both Unmarried"),
IIf([Married #1]="Married",([Borrower#1] & " Is Married"[B],""[/B]))))

the last ,"" is saying that if none of the above equations are true, then return a blank string