|
 |
access thread: Rats-Nested IIF Statement
Message #1 by Shelley Curnow <SCurnow@h...> on Wed, 17 Apr 2002 17:41:50 -0400
|
|
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C1E658.AE797330
Content-Type: text/plain;
charset="iso-8859-1"
Dear Friendly and Untired Eyes:
I am trying to accomplish some conditional formatting on a report in order
to save space, and am missing something critical in my IIF statement---like
a brain. Using fields from my contact table (HonorificID,
ContactNameFirst, ContactNameLast, ContactTitle, and ContactOrg), I want the
report to print whatever contact information exists on one line, formatted
so that it looks something like this:
If all the fields are populated: Mr. John Smith,
Executive Director / Foundation for Very Good Things
If the Org field is null: Mr. John Smith,
Executive Director
If the Title field is null: Mr. John Smith /
Foundation for Very Good Things
If the Title and Org fields are Null: Mr. John Smith
If the Last Name field is null: Executive Director /
Foundation for Very Good Things
If the Last Name and Org fields are null: Executive Director
If the Last Name and Title fields are null: Foundation for Very Good
Things
Here is the IIF statement I came up with, but, it's not working:
=IIF([ContactNameLast] Is Not Null, [HonorificID] & " " & [ContactNameFirst]
& " " & [ContactNameLast] & IIF([ContactTitle] Is Not Null, ", " &
[ContactTitle] & IIF([ContactOrg] Is Not Null, " / " & [ContactOrg], ""),
IIF([ContactOrg] Is Not Null, " / " & ContactOrg], "")),
IIF([ContactTitle] Is Not Null, [ContactTitle] & IIF([ContactOrg] Is Not
Null, " / " & [ContactOrg], ""), IIF([ContactOrg] Is Not Null,
[ContactOrg], "")))
This is the error I'm getting:
"The expression you entered contains invalid syntax. You omitted an operand
or operator, you entered an invalid character or comma, or you entered text
without surrounding it in quotation marks."
Heeeeeeeeeeelp. Please. Thank you.
--Shelley
Shelley Curnow
Grants and Data Administrator
F.B. Heron Foundation
tel: (xxx) xxx-xxxx
fax: (xxx) xxx-xxxx
Message #2 by "John Ruff" <papparuff@c...> on Wed, 17 Apr 2002 15:21:02 -0700
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0012_01C1E623.7CFC2DF0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Shelley,
The best way to handle this is to use VBA in your report.
1. Place the HonorificID, ContactNameFirst, ContactNameLast,
ContactTitle, and ContactOrg fields in the Detail Section of the report.
Set the visible property of each of the fields to No (you don't want the
fields to be seen).
2. Add a textbox to the report where the above information will be
displayed. Call the textbox txtName.
3. In the Detail Section's On Format event add the following code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim strName1 As String
Dim strName2 As String
strName1 = HonorificID & " " & ContactNameFirst
If Not IsNull(ContactNameLast) Then
If IsNull(ContactTitle) And IsNull(ContactOrg) Then
strName2 = strName1 & " " & ContactNameLast
ElseIf Not IsNull(ContactTitle) And Not IsNull(ContactOrg) Then
strName2 = strName1 & " " & ContactNameLast & ", " &
ContactTitle & "/" & ContactOrg
ElseIf Not IsNull(ContactTitle) Then
strName2 = strName1 & " " & ContactNameLast & ", " &
ContactTitle
ElseIf Not IsNull(ContactOrg) Then
strName2 = strName1 & " " & ContactNameLast & " / " &
ContactTitle
End If
ElseIf IsNull(ContactNameLast) Then
If Not IsNull(ContactTitle) And Not IsNull(ContactOrg) Then
strName2 = ContactTitle & "/" & ContactOrg
ElseIf Not IsNull(ContactTitle) Then
strName2 = ContactTitle
ElseIf Not IsNull(ContactOrg) Then
strName2 = ContactOrg
End If
End If
txtName = strName2
End Sub
This will solve all those iif statement problems.
Just a note - whenever I have a field on a form or report that is not
visible, I always change it's forecolor to red and it's backcolor to
yellow. That way I can instantly tell which fields are visible and
which fields are not visible when I'm in design mode.
John Ruff - The Eternal Optimist :-)
Always looking for Contract Opportunities
9306 Farwest Dr SW
Lakewood, WA 98498
papparuff@c...
-----Original Message-----
From: Shelley Curnow [mailto:SCurnow@h...]
Sent: Wednesday, April 17, 2002 2:42 PM
To: Access
Subject: [access] Rats-Nested IIF Statement
Dear Friendly and Untired Eyes:
I am trying to accomplish some conditional formatting on a report in
order to save space, and am missing something critical in my IIF
statement---like a brain. Using fields from my contact table
(HonorificID, ContactNameFirst, ContactNameLast, ContactTitle, and
ContactOrg), I want the report to print whatever contact information
exists on one line, formatted so that it looks something like this:
If all the fields are populated: Mr. John Smith,
Executive Director / Foundation for Very Good Things
If the Org field is null: Mr. John Smith,
Executive Director
If the Title field is null: Mr. John Smith
/ Foundation for Very Good Things
If the Title and Org fields are Null: Mr. John Smith
If the Last Name field is null: Executive Director /
Foundation for Very Good Things
If the Last Name and Org fields are null: Executive Director
If the Last Name and Title fields are null: Foundation for Very Good
Things
Here is the IIF statement I came up with, but, it's not working:
=IIF([ContactNameLast] Is Not Null, [HonorificID] & " " &
[ContactNameFirst] & " " & [ContactNameLast] & IIF([ContactTitle] Is Not
Null, ", " & [ContactTitle] & IIF([ContactOrg] Is Not Null, " / " &
[ContactOrg], ""), IIF([ContactOrg] Is Not Null, " / " & ContactOrg],
"")), IIF([ContactTitle] Is Not Null, [ContactTitle] & IIF([ContactOrg]
Is Not Null, " / " & [ContactOrg], ""), IIF([ContactOrg] Is Not Null,
[ContactOrg], "")))
This is the error I'm getting:
"The expression you entered contains invalid syntax. You omitted an
operand or operator, you entered an invalid character or comma, or you
entered text without surrounding it in quotation marks."
Heeeeeeeeeeelp. Please. Thank you.
--Shelley
Shelley Curnow
Grants and Data Administrator
F.B. Heron Foundation
tel: (xxx) xxx-xxxx
fax: (xxx) xxx-xxxx
---
Message #3 by "Andre Salem" <suzuki9@e...> on Wed, 17 Apr 2002 23:00:41 -0700
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0009_01C1E663.B2AE4240
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Rats-Nested IIF StatementHi shelley,
If you want to use IIF statement, I prefer using it with Yes or No,
T or F, my opinion, but with your report why do not you use IF Else and
End IF. or Select.
Regards
Andre
----- Original Message -----
From: Shelley Curnow
To: Access
Sent: Wednesday, April 17, 2002 2:41 PM
Subject: [access] Rats-Nested IIF Statement
Dear Friendly and Untired Eyes:
I am trying to accomplish some conditional formatting on a report in
order to save space, and am missing something critical in my IIF
statement---like a brain. Using fields from my contact table
(HonorificID, ContactNameFirst, ContactNameLast, ContactTitle, and
ContactOrg), I want the report to print whatever contact information
exists on one line, formatted so that it looks something like this:
If all the fields are populated: Mr. John Smith,
Executive Director / Foundation for Very Good Things
If the Org field is null: Mr. John
Smith, Executive Director
If the Title field is null: Mr. John
Smith / Foundation for Very Good Things
If the Title and Org fields are Null: Mr. John Smith
If the Last Name field is null: Executive Director
/ Foundation for Very Good Things
If the Last Name and Org fields are null: Executive Director
If the Last Name and Title fields are null: Foundation for Very Good
Things
Here is the IIF statement I came up with, but, it's not working:
=3DIIF([ContactNameLast] Is Not Null, [HonorificID] & " " &
[ContactNameFirst] & " " & [ContactNameLast] & IIF([ContactTitle] Is Not
Null, ", " & [ContactTitle] & IIF([ContactOrg] Is Not Null, " / " &
[ContactOrg], ""), IIF([ContactOrg] Is Not Null, " / " & ContactOrg],
"")), IIF([ContactTitle] Is Not Null, [ContactTitle] & IIF([ContactOrg]
Is Not Null, " / " & [ContactOrg], ""), IIF([ContactOrg] Is Not Null,
[ContactOrg], "")))
This is the error I'm getting:
"The expression you entered contains invalid syntax. You omitted an
operand or operator, you entered an invalid character or comma, or you
entered text without surrounding it in quotation marks."
Heeeeeeeeeeelp. Please. Thank you.
--Shelley
Shelley Curnow
Grants and Data Administrator
F.B. Heron Foundation
tel: (xxx) xxx-xxxx
fax: (xxx) xxx-xxxx
Message #4 by joe.dunn@c... on Thu, 18 Apr 2002 09:48:28 +0000
|
|
Shelley Curnow posted a query regarding multiple IIFs not working:
=3DIIF([ContactNameLast] Is Not Null, [HonorificID] & " " &
[ContactNameFirst] & " " & [ContactNameLast] & IIF([ContactTitle] Is No
t
Null, ", " & [ContactTitle] & IIF([ContactOrg] Is Not Null, "=A0 /=A0 "
&
[ContactOrg], ""), IIF([ContactOrg] Is Not Null, "=A0 /=A0 " & ContactO
rg],
"")), IIF([ContactTitle] Is Not Null, [ContactTitle] & IIF([ContactOrg]
Is
Not Null, "=A0 /=A0 " & [ContactOrg], ""), IIF([ContactOrg] Is Not Null
,
[ContactOrg], "")))
My first reaction is that I always test for NULL in a function as follo
ws:
IIF(not IsNull([ContactNameLast], .......
in other words I use the IsNull function and combine it with the NOT
keyword. Bit of a roundabout way of stating the requirement but as far
as I
now that is the correct method.
It may be clearer to use a function - but here you have to handle the
possibility of NULL fields because you cannot pass NULLS to a function
so
you have to pass the field plus a space (" ").
The function could be:
Public Function CollateContactInfo(pHONORIFIC as string, pCNAMELAST as
string, pCNAMEFIRST as string, _
pTITLE as string, pCONTACTORG as string) as string
' collates elements of contact info omitting zero length strings
' Accepts: pHONORIFIC
' pCNAMEFIRST
' pCNAMELAST
' pTITLE
' pCONTACTORG
' Returns: string with collated results
' (e.g. Mr. John Smith, Executive Director / Foundation for Ver
y
Good Things)
Dim RETURNSTRING as string, HAVENAME as Boolean, HAVETITLE as Boolean
if Len(Trim(pCNAMELAST)) > 0 then
RETURNSTRING =3D Trim(pHONORIFIC) & " " & Trim(pCNAMEFIRST) & " " &
Trim(pCNAMELAST)
HAVENAME =3D True
Else
HAVENAME =3D False
end if
if Len(Trim(pTITLE)) > 0 then
HAVETITLE =3D True
if HAVENAME =3D True then
RETURNSTRING =3D RETURNSTRING & "," & Trim(pTITLE)
else
RETURNSTRING =3D Trim(pTITLE)
end if
else
HAVETITLE =3D False
end if
if Len(Trim(pCONTACTORG)) > 0 then
if HAVENAME =3D True or HAVETITLE =3D True then
RETURNSTRING =3D RETURNSTRING & "/" & Trim(pCONTACTORG)
else
RETURNSTRING =3D Trim(pCONTACTORG)
end if
end if
End Function
You then call the function where required as:
=3DCollateContactInfo( [HonorificID], [ContactNameFirst], [ContactName
Last]
, [ContactTitle], [ContactOrg])
I hope this helps - I have not tested it but I have done this many time
s
and it is clearer when written as a function which you can then use aga
in
and again and again...
Joe Dunn
*************************************************************************
This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.
The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965 - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL Telephone 0161-832-8686 Internet http://www.cis.co.uk E-mail
cis@c...
CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.
CIS is a member of the General Insurance Standards Council
CIS & the CIS logo (R) Co-operative Insurance Society Limited
********************************************************************************
Message #5 by Shelley Curnow <SCurnow@h...> on Thu, 18 Apr 2002 09:24:11 -0400
|
|
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C1E6DC.53CE57F0
Content-Type: text/plain
John, Joe, Andre...
Thanks so much! The If, Then, ElseIf statements work MUCH MUCH better!
Niiiiice. I still haven't gotten to the point where I naturally resort to
VBA solutions. But I'll get there.
Anyway...the rats-nests are gone.
Thank you!
sc
Shelley Curnow
Grants and Data Administrator
F.B. Heron Foundation
tel: (xxx) xxx-xxxx
fax: (xxx) xxx-xxxx
-----Original Message-----
From: John Ruff [mailto:papparuff@c...]
Sent: Wednesday, April 17, 2002 5:21 PM
To: Access
Subject: [access] RE: Rats-Nested IIF Statement
Shelley,
The best way to handle this is to use VBA in your report.
1. Place the HonorificID, ContactNameFirst, ContactNameLast, ContactTitle,
and ContactOrg fields in the Detail Section of the report. Set the visible
property of each of the fields to No (you don't want the fields to be seen).
2. Add a textbox to the report where the above information will be
displayed. Call the textbox txtName.
3. In the Detail Section's On Format event add the following code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim strName1 As String
Dim strName2 As String
strName1 = HonorificID & " " & ContactNameFirst
If Not IsNull(ContactNameLast) Then
If IsNull(ContactTitle) And IsNull(ContactOrg) Then
strName2 = strName1 & " " & ContactNameLast
ElseIf Not IsNull(ContactTitle) And Not IsNull(ContactOrg) Then
strName2 = strName1 & " " & ContactNameLast & ", " &
ContactTitle & "/" & ContactOrg
ElseIf Not IsNull(ContactTitle) Then
strName2 = strName1 & " " & ContactNameLast & ", " &
ContactTitle
ElseIf Not IsNull(ContactOrg) Then
strName2 = strName1 & " " & ContactNameLast & " / " &
ContactTitle
End If
ElseIf IsNull(ContactNameLast) Then
If Not IsNull(ContactTitle) And Not IsNull(ContactOrg) Then
strName2 = ContactTitle & "/" & ContactOrg
ElseIf Not IsNull(ContactTitle) Then
strName2 = ContactTitle
ElseIf Not IsNull(ContactOrg) Then
strName2 = ContactOrg
End If
End If
txtName = strName2
End Sub
This will solve all those iif statement problems.
Just a note - whenever I have a field on a form or report that is not
visible, I always change it's forecolor to red and it's backcolor to yellow.
That way I can instantly tell which fields are visible and which fields are
not visible when I'm in design mode.
John Ruff - The Eternal Optimist :-)
Always looking for Contract Opportunities
9306 Farwest Dr SW
Lakewood, WA 98498
papparuff@c...
-----Original Message-----
From: Shelley Curnow [mailto:SCurnow@h...]
Sent: Wednesday, April 17, 2002 2:42 PM
To: Access
Subject: [access] Rats-Nested IIF Statement
Dear Friendly and Untired Eyes:
I am trying to accomplish some conditional formatting on a report in order
to save space, and am missing something critical in my IIF statement---like
a brain. Using fields from my contact table (HonorificID,
ContactNameFirst, ContactNameLast, ContactTitle, and ContactOrg), I want the
report to print whatever contact information exists on one line, formatted
so that it looks something like this:
If all the fields are populated: Mr. John Smith,
Executive Director / Foundation for Very Good Things
If the Org field is null: Mr. John Smith,
Executive Director
If the Title field is null: Mr. John Smith /
Foundation for Very Good Things
If the Title and Org fields are Null: Mr. John Smith
If the Last Name field is null: Executive Director /
Foundation for Very Good Things
If the Last Name and Org fields are null: Executive Director
If the Last Name and Title fields are null: Foundation for Very Good
Things
Here is the IIF statement I came up with, but, it's not working:
=IIF([ContactNameLast] Is Not Null, [HonorificID] & " " & [ContactNameFirst]
& " " & [ContactNameLast] & IIF([ContactTitle] Is Not Null, ", " &
[ContactTitle] & IIF([ContactOrg] Is Not Null, " / " & [ContactOrg], ""),
IIF([ContactOrg] Is Not Null, " / " & ContactOrg], "")),
IIF([ContactTitle] Is Not Null, [ContactTitle] & IIF([ContactOrg] Is Not
Null, " / " & [ContactOrg], ""), IIF([ContactOrg] Is Not Null,
[ContactOrg], "")))
This is the error I'm getting:
"The expression you entered contains invalid syntax. You omitted an operand
or operator, you entered an invalid character or comma, or you entered text
without surrounding it in quotation marks."
Heeeeeeeeeeelp. Please. Thank you.
--Shelley
Shelley Curnow
Grants and Data Administrator
F.B. Heron Foundation
tel: (xxx) xxx-xxxx
fax: (xxx) xxx-xxxx
---
---
|
|
|
View Cart
