0

I am new to Access but, thanks to this great forum, I have learned a lot in the past few weeks.

I am trying to make an image control to display one of three arrow images depending on a value of "1,2 or 3" in another field. The arrows represent market trends for the current record. The arrows are Up (value = 1, image="cmdTrendUp"), Sideways (value = 2, image="cmdTrendNeutral") and Down (value = 3, image="cmdTrendDown"). (The images are labeled "cmd" because the same images are shared with buttons on another form.)

I used nested IIF functions as an expression. The image for a value of "1" shows up as expected. However when I cycle through the records only the Green/Up arrow is displayed. Can you help me determine what is wrong?

=IIf([DEMO_UrlMsaCountyCAmq_MuniList]![TrendarrowValue]=1,[cmdTrendUp],IIf([DEMO_UrlMsaCountyCAmq_MuniList]![TrendarrowValue]=2,[cmdTrendNeutral],IIf([DEMO_UrlMsaCountyCAmq_MuniList]![TrendarrowValue]=3,[cmdTrendDown],[Anicon Orb.png])))
D2rd
  • 11
  • 7

2 Answers2

0

Use the Choose function

Quoted from the Office Help:

Syntax Choose(index, choice-1[, choice-2, ... [, choice-n]])

The Choose function syntax has these parts: Part Description index Required. Numeric expression or field that results in a value between 1 and the number of available choices. choice Required. Variant expression containing one of the possible choices. Remarks

Choose returns a value from the list of choices based on the value of index. If index is 1, Choose returns the first choice in the list; if index is 2, it returns the second choice, and so on. You can use Choose to look up a value in a list of possibilities. For example, if index evaluates to 3 and choice-1 = "one", choice-2 = "two", and choice-3 = "three", Choose returns "three". This capability is particularly useful if index represents the value in an option group. Choose evaluates every choice in the list, even though it returns only one. For this reason, you should watch for undesirable side effects. For example, if you use the MsgBox function as part of an expression in all the choices, a message box will be displayed for each choice as it is evaluated, even though Choose returns the value of only one of them. The Choose function returns a Null if index is less than 1 or greater than the number of choices listed. If index is not a whole number, it is rounded to the nearest whole number before being evaluated.

Example This example uses the Choose function to display a name in response to an index passed into the procedure in the Ind parameter.

Function GetChoice(Ind As Integer) GetChoice = Choose(Ind, "Speedy", "United", "Federal") End Function

© 2010 Microsoft Corporation. All rights reserved.

E Mett
  • 2,272
  • 3
  • 18
  • 37
  • How fortuitous! I read about this function on my way home this evening. Thank you I will try this tomorrow. – D2rd Aug 20 '13 at 05:47
  • I tried to implement this answer with no joy. The CHOOSE expression I put in the ControlSource property of the image control. Was this the correct place? When I use a textbox as the target with the choices being "1-Green", "2-Yellow", etc. I get a #type# error in the box: [code]=Choose([TrendarrowValue],"1-Green", "2-Yellow", "3-Red")[/code] evaluated to "#type# error " OR [code]=Choose([TrendarrowValue],1, 2, 3)[/code] evaluated to "#type# error " I also tried "AfterUpdate" in the expression with no joy. What am I missing? Thanks in advance. – D2rd Aug 21 '13 at 04:15
  • The images are in custom icons that I have stored as a shared resource in the Image Gallery. How do I reference them there? – D2rd Aug 22 '13 at 05:05
  • I will try to explain the relationships more clearly: The rows in table DEMO_UrlMsaCountyCAmq_MuniList contains several fields describing a city's current and past real estate market metrics. Each row is a different city. The trigger field named "PercentChange" is a calculated field that evaluates CurrentPrice against PriorPrice expressed as a +/- percentage. The index field TrendArrowValue evaluates PercentChange and stores one of three values (1,2 or 3) as integers: If PercentChange >0 TrendArrowValue = 1 If PercentChange = 0 TrendArrowValue = 2 and If PercentChange <0 TrendArrowValue = 3. – D2rd Aug 22 '13 at 05:08
0

Create a table with autonumber field and an image field. Store the images there with their respective indexes.

Add this table to the underlying query using the index(1,2,3) as a join.

Bind the image control to the image field in the underlying query.

E Mett
  • 2,272
  • 3
  • 18
  • 37
  • For each row TrendArrowValue has one of those three values. The integer in TrendArrowValue tells MyImageControl which image to display for the current record: 1 = GreenArrow.png (Upward trend), 2 = YellowArrow.png (Neutral trend) and 3 = RedArrow.png (Downward trend). The images are stored in the Image Gallery because they are used in many forms and reports. MyImageControl is supposed to read that value and display the associated image. When the record pointer moves to the next record MyImageControl should update its image as required. – D2rd Aug 22 '13 at 05:08
  • Based on the answers I have received so far I have tried: Using the Choose() function as an expression behind MyImageControl.Picture CODE [indent] = Choose(TrendArrowValue,GreenArrow.png,YellowArrow.png,RedArrow.png)'[/indent] Using the IIF() function as an expression behind MyImageControl.Picture CODE IIF([DEMO_UrlMsaCountyCAmq_MuniList]![TrendarrowValue]=1, [cmdTrendUp], IIF([DEMO_UrlMsaCountyCAmq_MuniList]![TrendarrowValue]=2, [cmdTrendNeutral], IIF([DEMO_UrlMsaCountyCAmq_MuniList]![TrendarrowValue]=3,[cmdTrendDown],[Anicon Orb.png]))) – D2rd Aug 22 '13 at 05:09
  • Referencing the objects using "Me.SomeObject.Picture" and Me!SomeObject.PictureI desperately need guidance on the proper syntax and where to place it in the form and/or control. – D2rd Aug 22 '13 at 05:10