Results 1 to 6 of 6
  1. #1
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46

    Easier (Better) Way to do this?

    I have a report that has several of these IIf statements for changing the photo depending on the date.



    It works but seems like it is just the "wrong" way to do it. One reason is because the only way to change the data (Photo) is by going to each statement on the report design view to do it. Plus it is a pain to code this for every photo slot. And it does take some time for the report to paginate when I print it, but not sure if it is the code, but I suspect it is because the report is only about 10 pages.

    Problem with the report it is from a Cross-tab Query so I can't really (or at least I don't know how to) add these fields to that query.


    Thought I would throw it out there and see what others think.

    Code:
    =IIf([Expr1] Between #7/5/2016# And #8/16/2016#,"\\Server\Dir\Subdir\photo1.png",IIf([Expr1] Between #8/17/2016# And #8/30/2016#,"\\Server\Dir\Subdir\photo2.png",IIf([Expr1] Between #8/31/2016# And #9/27/2016#,"\\Server\Dir\Subdir\photo3.png",IIf([Expr1] Between #9/28/2016# And #10/7/2016#,"\\Server\Dir\Subdir\photo4.png",IIf([Expr1] Between #10/8/2016# And #10/27/2016#,"\\Server\Dir\Subdir\photo5.png",IIf([Expr1] Between #10/28/2016# And #11/17/2016#,"\\Server\Dir\Subdir\photo6.png",IIf([Expr1] Between #11/18/2016# And #11/30/2016#,"\\Server\Dir\Subdir\photo7.png","\\Server\Dir\Subdir\photoblank.png")))))))

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    My first thought is a table with beginning and ending date fields and a text field for the path. You can use any number of methods to get the path, and it becomes user-maintainable.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46
    Thought about that.

    Then still use the IIf statement to reference the Table? Or use another method?

    Seems like I would still have an excessive IIf statement, but maybe no way to avoid that?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No IIf(). A DLookup() or recordset with criteria, or a query with a non-equi join.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46
    How does that pass back to the report?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The closed equivalent:

    =DLookup(...)

    DLookup Usage Samples

    You could also use a recordset in VBA, or join to that table in the report's source query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. The Golden VB Script that will make your life much easier
    By pkstormy in forum Code Repository
    Replies: 55
    Last Post: 04-14-2015, 12:08 PM
  2. There has to be an easier / better way
    By Derrick T. Davidson in forum Queries
    Replies: 14
    Last Post: 07-10-2014, 07:59 PM
  3. Easier approach to gaining a sumation
    By GraeagleBill in forum Programming
    Replies: 5
    Last Post: 08-22-2013, 10:15 AM
  4. Easier way than adding 200 queries?
    By Kamal in forum Queries
    Replies: 2
    Last Post: 02-28-2013, 07:02 AM
  5. Replies: 8
    Last Post: 08-11-2010, 09:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums