graphic issue (1 Viewer)

[June7]

Put whatever you want in place of the Null.

Did you review arnelgp's example?

Provide example of the actual data table. Does Mdid value or any part of it compose part of the image name? Is there consistency in structure of image names? If path can be built from data in field, might not need conditional expression.

Could provide your db as suggested. And some images.

 

[kitty77]

Put whatever you want in place of the Null.

Did you review arnelgp's example?

Provide example of the actual data table. If path can be built from data in field, might not need conditional expression. Does Mdid value or any part of it compose part of the image name? Is there consistency in structure of image names?

Got it. No, Mdid is not part of the image name. Mdid is an ID of customers that would have a unique image. Probably about 8 different possibilies otherwise the default image. So, if you could show me an example using three different Mdid values.

Thanks.

 

[June7]

arnelgp has already shown you an example. Switch() function is better than IIf() in this case. Adjust it for your actual data and image names.

You have only 8 customers with specific image? What happens when you get more? Maybe you should rename images to use ID.

 

[kitty77]

arnelgp has already shown you an example. Switch() function is better than IIf() in this case. Adjust it for your actual data and image names.

You have only 8 customers with specific image? What happens when you get more? Maybe you should rename images to use ID.

No, we have many customers, only 8 or so have a unique background image on their report. Otherwise, it will use our standard background image.

 

[June7]

You still have issue of what to do when you get more customers with customized image. Have to modify expression. Is this a split design db with multiple users?

 

[kitty77]

You still have issue of what to do when you get more customers with customized image. Have to modify expression. Is this a split design db with multiple users?

No, these are master customers using the same ID numbers several times. So, it will only be a few with unique backgrounds.

 

[kitty77]

You still have issue of what to do when you get more customers with customized image. Have to modify expression. Is this a split design db with multiple users?

So, using your example, I can't combine them into one statement?

=IIf([Mdid] = "ABC123", "\\Reportbackgrounds\ABCreport1.png", Null)
=IIf([Mdid] = "DEF123", "\\Reportbackgrounds\ABCreport2.png", Null)
=IIf([Mdid] = "GHI123", "\\Reportbackgrounds\ABCreport3.png", Null)

 

[June7]

You can do nested IIf() but again, the Switch() version is better.

=IIf([Mdid] = "ABC123", "\\Reportbackgrounds\ABCreport1.png", IIf([Mdid] = "DEF123", "\\Reportbackgrounds\ABCreport2.png", IIf([Mdid] = "GHI123", "\\Reportbackgrounds\ABCreport3.png", "default image name")))

or

="\\Reportbackgrounds\ABCreport" & IIf([Mdid] = "ABC123", "1", IIf([Mdid] = "DEF123", "2", IIf([Mdid] = "GHI123", "3", "default image name"))) & ".png"

or

="\\Reportbackgrounds\ABCreport" & Switch([Mdid] = "ABC123", "1", [Mdid] = "DEF123", "2", [Mdid] = "GHI123", "3", True, "default image name") & ".png"

 

[kitty77]

You can do nested IIf() but again, the Switch() version is better.

=IIf([Mdid] = "ABC123", "\\Reportbackgrounds\ABCreport1.png", IIf([Mdid] = "DEF123", "\\Reportbackgrounds\ABCreport2.png", IIf([Mdid] = "GHI123", "\\Reportbackgrounds\ABCreport3.png", "default image name)))

or

="\\Reportbackgrounds\ABCreport" & IIf([Mdid] = "ABC123", "1", IIf([Mdid] = "DEF123", "2", IIf([Mdid] = "GHI123", "3", "default image name))) & ".png"

or

="\\Reportbackgrounds\ABCreport" & Switch([Mdid] = "ABC123", "1", [Mdid] = "DEF123", "2", [Mdid] = "GHI123", "3", True, "default image name") & ".png"

Will try them all. Much appreciated!!

 

[June7]

There is a limit on number of characters in ControlSource and query cell - 1,024 I think. A long expression might exceed that. Then use a custom function to build and return image path.

 

[kitty77]

There is a limit on number of characters in ControlSource and query cell - 1,024 I think. A long expression might exceed that. Then use a custom function to build and return image path.

custom function?

 

[June7]

VBA code.

 

[kitty77]

VBA code.

got it.

 

[kitty77]

VBA code.

This works just fine but how do handle the default image if it does not meet one of those. I know it's where Null is.
The default would be "\\Reportbackgrounds\3report.png"

I must be missing something. Can you show me how that would look?

=IIf([Mdid] = "1234*","\\Reportbackgrounds\1report.png",IIf([Mdid]="ABC","\\Reportbackgrounds\2report.png",Null))

 

[June7]

Wildcard needs LIKE operator. Mdid value could be "ABC"?

=IIf([Mdid] LIKE "1234*", "\\Reportbackgrounds\1report.png", IIf([Mdid]="ABC", "\\Reportbackgrounds\2report.png", "\\Reportbackgrounds\3report.png"))

 

[kitty77]

Wildcard needs LIKE operator. Mdid value could be "ABC"?

=IIf([Mdid] LIKE "1234*", "\\Reportbackgrounds\1report.png", IIf([Mdid]="ABC", "\\Reportbackgrounds\2report.png", "\\Reportbackgrounds\3report.png"))

Perfect, that worked. I was missing a quote. If I wanted to use an "or" how would that look?

=IIf([Mdid]="1234" or "abc","\\Reportbackgrounds\1report.png" I tried this but does not work...

 

[June7]

You used wildcard with "1234*", why would you not still need it? What values does Mdid hold?

=IIf([Mdid] LIKE "1234*" OR [Mdid] = "ABC", "\\Reportbackgrounds\1report.png", "\\Reportbackgrounds\3report.png")

 

[kitty77]

You used wildcard with "1234*", why would you not still need it? What values does Mdid hold?

=IIf([Mdid] LIKE "1234*" OR [Mdid] = "ABC", "\\Reportbackgrounds\1report.png", "\\Reportbackgrounds\3report.png")

Thanks. I really appreciate all your help.

 

[June7]

Consider:

="\\Reportbackgrounds\" & IIf([Mdid] LIKE "1234*" OR [Mdid] = "ABC", 1, 3) & "report.png"

 

[kitty77]

Consider:

="\\Reportbackgrounds\" & IIf([Mdid] LIKE "1234*" OR [Mdid] = "ABC", 1, 3) & "report.png"

Perfect