UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Nested Iif Structure In Text Box On Report, Access 2016    
 
   
brastedhouse
post Oct 17 2019, 04:46 PM
Post#1



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


I have a report that is driven by a query. The query has two fields that are populated based on the entry of a textbox and combox on an unbound form. I want to put the entry of the combobox in a textbox in the header of the report. That is working fine, but the data is the ID field and I need it to be in a human readable form as EMS, Fire, MVA and Other or, if Null (left blank) All Calls instead of 1,2 3, or 5.

This is an exploded view of the =IIf statement. I exploded it to make sure I did not miss anything. But apparently I did. I m getting an operand error when I open the report.

Thanks in advance for the help.

=IIf( (
([Forms]![frmDeptMemberCallCountAll]![cboCallType])
,1
,"EMS"),
IIf(
([Forms]![frmDeptMemberCallCountAll]![cboCallType])
,2
,"Fire"),
IIf(
([Forms]![frmDeptMemberCallCountAll]![cboCallType])
,3
,"MVA"),
IIf(
([Forms]![frmDeptMemberCallCountAll]![cboCallType])
,5
,"Other),
IIf
(IsNull
(([Forms]![frmDeptMemberCallCountAll]![cboCallType])
,"All Calls")
)
)
Go to the top of the page
 
theDBguy
post Oct 17 2019, 05:01 PM
Post#2


UA Moderator
Posts: 76,889
Joined: 19-June 07
From: SunnySandyEggo


Hi. An IIf() condition is typically a comparison, so maybe you're missing that. For example: IIf(Something = SomeValue, TrueResult, FalseResult). Right now, you don't have any equal signs.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
orange999
post Oct 17 2019, 06:31 PM
Post#3



Posts: 1,999
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


You might want to research the Option Group control in ms access.
If a user can only select 1 option, then the Option Group control on your form may be applicable.

--------------------
Good luck with your project!
Go to the top of the page
 
brastedhouse
post Oct 17 2019, 07:11 PM
Post#4



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


Hi all, thanks for the thoughts. Using DBGuys answer and scouring nested iif statement results on google this works until I add the final statement that ends "All Calls". Can anyone tell me how to approach the final of none of the above then "All Calls" statement?

=IIf([Forms]![frmDeptMemberCallCountAll]![cboCallType]=1,"EMS",IIf([Forms]![frmDeptMemberCallCountAll]![cboCallType]=2,"Fire",IIf([Forms]![frmDeptMemberCallCountAll]![cboCallType]=3,"MVA",IIf([Forms]![frmDeptMemberCallCountAll]![cboCallType]=5,"Other",IIf([Forms]![frmDeptMemberCallCountAll]![cboCallType]=" ","All Calls")))))

Best, Scott
Go to the top of the page
 
brastedhouse
post Oct 17 2019, 07:32 PM
Post#5



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


OK, so I have it figured out and it works fine. Below is how I did it.

Thanks to all who prodded me to keep looking and thinking and trying.

Best, Scott

CODE
=IIf(IsNull([Forms]![frmDeptMemberCallCountAll]![cboCallType]),"All Calls",IIf([Forms]![frmDeptMemberCallCountAll]![cboCallType]=1,"EMS",IIf([Forms]![frmDeptMemberCallCountAll]![cboCallType]=2,"Fire",IIf([Forms]![frmDeptMemberCallCountAll]![cboCallType]=3,"MVA",IIf([Forms]![frmDeptMemberCallCountAll]![cboCallType]=5,"Other")))))
Go to the top of the page
 
theDBguy
post Oct 17 2019, 08:05 PM
Post#6


UA Moderator
Posts: 76,889
Joined: 19-June 07
From: SunnySandyEggo


Hi. Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Oct 17 2019, 08:07 PM
Post#7



Posts: 6,184
Joined: 11-November 10
From: SoCal, USA


QUOTE
a textbox and combox on an unbound form. I want to put the entry of the combobox in a textbox in the header of the report. That is working fine, but the data is the ID field and I need it to be in a human readable form as EMS, Fire, MVA and Other

QUOTE
=IIf(IsNull([Forms]![frmDeptMemberCallCountAll]![cboCallType]),"All Calls",IIf([Forms]![frmDeptMemberCallCountAll]![cboCallType]=1,"EMS",IIf([Forms]![frmDeptMemberCallCountAll]![cboCallType]=2,"Fire",IIf([Forms]![frmDeptMemberCallCountAll]![cboCallType]=3,"MVA",IIf([Forms]![frmDeptMemberCallCountAll]![cboCallType]=5,"Other")))))

there's nothing wrong with your solution on the face of it, but there is no expandability there. i suspect that we can add in that expandability while at the same time making the whole thing much simpler.

are you populating the combobox control's RowSource with a query or SQL statement? if the former, will you post the query's SQL statement? if the latter, will you post the SQL statement directly from the RowSource?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
brastedhouse
post Oct 17 2019, 09:04 PM
Post#8



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


Hi, I'm not sure I understand what expandability there can be. This is a list that is very unlikely to change. If it did, the data comes from a table that the SQL uses to populate the cbo. Here is the SQL for the cbo, it's pretty simple.

CODE
SELECT tblLUCallType.callTypeID, tblLUCallType.callType
FROM tblLUCallType
ORDER BY tblLUCallType.[callTypeID];


I'm curious about what your idea is. I am always willing to learn how to do things better. I originally was going to try and use a VBA Case statement, but it got more complicated than I was willing to deal with.

Thanks, Tina.

Best, Scott
Go to the top of the page
 
arnelgp
post Oct 17 2019, 09:06 PM
Post#9



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


Choose() function is very small rather than bunch of IIfs
CODE
=Choose(Val([Forms]![frmDeptMemberCallCountAll]![cboCallType] & "")+1,"All Calls","EMS","Fire","MVA","Other")

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
brastedhouse
post Oct 17 2019, 09:18 PM
Post#10



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


Well that is slick. Can it accommodate the none of the above scenario? If the cbo is left blank, I need it say "All Calls". Is there a way to say Else If . . . with the Choose () function?
Go to the top of the page
 
brastedhouse
post Oct 17 2019, 09:49 PM
Post#11



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


Never mind, I need to get my eyes checked. Thanks again.
Go to the top of the page
 
PaulBrand
post Oct 18 2019, 12:50 AM
Post#12



Posts: 1,749
Joined: 4-September 02
From: Oxford UK


You should also check out the Switch function, which has an 'Else' argument.

--------------------
Paul
Go to the top of the page
 
brastedhouse
post Oct 18 2019, 02:34 PM
Post#13



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


Thanks for all the new reading and learning material. If I ever switch (excuse the pun) to SQL from Access, will these functions pose any problems. I have spent the last 6 months getting rid of multivalue and multivalue lookup fields in anticipation of future SQL moves. It has been quite a chore. I hope to never see another DB with those fields in it.

Thanks. Scott
Go to the top of the page
 
arnelgp
post Oct 18 2019, 03:13 PM
Post#14



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


these are all MSA functions, if you're Front end is MSA then they will function even
if the BE is MSSQL/MySQL.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
tina t
post Oct 18 2019, 04:00 PM
Post#15



Posts: 6,184
Joined: 11-November 10
From: SoCal, USA


QUOTE
SELECT tblLUCallType.callTypeID, tblLUCallType.callType

okay, good. as i guessed/hoped, you have two columns in the combobox: the bound column holding the callTypeID value, and a second column holding the text description, as field callType. so the user sees the text value in the combobox control, and chooses a text value, and the number value is held in the control.

the cool, and sneaky, thing about combobox controls is that any column that you include in the RowSource is available to be referenced, even if the column itself is not visible, and/or is not the bound column. so try changing the IIf() function in the report to

=IIf([Forms]![frmDeptMemberCallCountAll]![cboCallType] Is Null,"All Calls",[Forms]![frmDeptMemberCallCountAll]![cboCallType].Column(1))

now the expression is automatically expandable. whether you ever add, or change, a call type in the table, or not, it doesn't matter. the expression will handle whatever value is pulled from the combobox control's RowSource, without ever having to be updated.

it's possible that Access will balk at reading the combobox control's Column property in the report. so try the above first, and make sure you refer to Column(1). combobox (and listbox) columns are zero-based, so the first column (reading left to right in the RowSource) is 0, the second column is 1, the third is 2, etc.

if the direct reference doesn't work, then add an unbound textbox control to the form, i'll call it txtCallType. set it's Visible property to No/False. set the textbox control's ControlSource property to

=cboCallType.Column(1)

Access will probably add brackets, no problem. then change the IIf() expression in the report to

=IIf([Forms]![frmDeptMemberCallCountAll]![cboCallType] Is Null,"All Calls",[Forms]![frmDeptMemberCallCountAll]![txtCallType])

hth
tina
This post has been edited by tina t: Oct 18 2019, 04:01 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
brastedhouse
post Oct 18 2019, 04:10 PM
Post#16



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


again, thanks
Go to the top of the page
 
NimishParikh
post Oct 19 2019, 12:47 AM
Post#17



Posts: 235
Joined: 30-November 10



Can you make intermediate table that has a field for the value of combobox and corresponding return value as another field and then return value using query on newly conceived table.

Nimish
This post has been edited by NimishParikh: Oct 19 2019, 12:48 AM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th December 2019 - 05:19 AM