[X]My Assistant

[brastedhouse]

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")
)
)

[theDBguy]

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.

[orange999]

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.

[brastedhouse]

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

[brastedhouse]

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")))))

[theDBguy]

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

[tina t]

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

=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

[brastedhouse]

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

[arnelgp]

Choose() function is very small rather than bunch of IIfs

CODE

=Choose(Val([Forms]![frmDeptMemberCallCountAll]![cboCallType] & "")+1,"All Calls","EMS","Fire","MVA","Other")

[brastedhouse]

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?

[brastedhouse]

Never mind, I need to get my eyes checked. Thanks again.

[PaulBrand]

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

[brastedhouse]

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

[arnelgp]

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

[tina t]

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

[brastedhouse]

again, thanks

[NimishParikh]

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