[AccessD] IFF else problem

Jim Hewson JHewson at karta.com
Mon Aug 16 13:43:11 CDT 2004


Kathryn,
On another thread, there was a discussion of code review.
I did a code review of my own suggestion and saw that I left something out.

The correct suggestion:
Name:  FirstName & " " & IIF([Female]="Yes", "(" & [Surname] & ") " & [MarrName], [Surname])

The second parenthesis does have a space after it to accommodate the space before the MarrName.

Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Hewson
Sent: Monday, August 16, 2004 1:23 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] IFF else problem



Kathryn,
How about this:

Name:  FirstName & IF([Female]="Yes", "(" & [Surname] & ") " & [MarrName], [Surname])

HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of KathrynAtGwens
Sent: Monday, August 16, 2004 1:07 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] IFF else problem


in query, I have an existing 
Expr1: [FirstName] & " " & [Surname] & " " & [MarrName]

I have a field called Female that can be True or False.

What I want is that IF [Female] is true, then I want:
FirstName (Surname) MarrName
Jane (Smith) Doe

otherwise (if Female is False) then I want
FirstName Surname
John Doe

Based on my previously giving Drew a slightly different scenario of having the married name last in parens, like
Jane Smith (Doe), he gave me this (though I've modified [Sex] being F to [Female] being True)
IFF([Female]="True",IIF(Len([MarrName])>0),"(" & [MarrName] & ")",""),"")

But no matter how I try to combine everything in the query, I can't get my results correct. I've either got too many of this, or not enough of that or undefined functions

I know I can leave out the length portion because that is no longer needed since I want the () around the surname of females, and surname is always filled in. Here is my most recent attempt without regards to the males:

Expr1: IFF([Female]="True", [FirstName] & "(" & [Surname] & ")" & [MarrName])
which does not give me any invalid syntax errors, but does give me Undefined Function IIF in expression. I assume that has something to do with defining the "else" portion.

So, what do I need to add? 

--
Kathryn Bassett (at work address)
kathrynatgwens at socal.rr.com
kathryn at bassett.net (home) 

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list