Results 1 to 13 of 13
  1. #1
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77

    How to make IIf statement return value from a specific table

    I have a query with an IIf statement which makes some calculations for the user, based on values other places in the query. The IIF statement returns a piece of text which the user can read.
    So far, this works just fine.
    Now, I have two problems which I seek to solve in a certain way.
    First, the IIF statement becomes very long. Second, I would like to format the returned text string.

    I believe the solution to both may be to have the IIf statement return a value from another table. In this other table, I can make long text strings and I can format them too, without the IIf statement becoming very long.

    But, how do I ask the IIf statement to return a value which is placed in a table?

    Has it something to do with lookup, and how do I use that?

    Thank you in advance.


    EDIT: Thought I'd add information about the value I'm trying to have the IIf statement return.
    Let's say I have a table called Table1. In Table1, there are two columns, Id and Returntext. There are a number of rows, with Id going from 1 to 5, and Returntext having the needed information.
    I am trying to create an IIf statement where the returned value (if statement is true) is Returntext, corresponding to a patricular Id. For instance, if the IIf statement is TRUE, then return the text in Returntext corresponding to Id=1, and if the IIf statement is FALSE, then return the text in Returntext corresponding to Id=2.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    rather than providing hypothetical information, suggest

    a) explain what you want to do (not how you think it should be done) in simple English e.g. 'I'm writing a report which is in the form of a letter and I need to place key words such as place name, name of the recipient, etc in the letter text'
    b) provide your table(s) and relevant field names with relationships
    c) provide some example data and the outcome required
    d) provide the sql to the query you are trying to use at the moment

    Your iif may be the way to go but impossible to say from what you have provided, often there are better ways

    Or if you just want a very simple answer based on what you have provided

    But, how do I ask the IIf statement to return a value which is placed in a table?

    Has it something to do with lookup, and how do I use that?
    - use the dlookup function, this is a link on how to use it https://support.office.com/en-gb/art...b-bed10dca5937. However it is not recommended to use domain functions in queries. Alternatively join the tables together

  3. #3
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77
    Let me try. I cannot recreate all of it here, but this is the basis of what I do:

    I have a TableCountries. In TableCountries, there is a column named Country, with names of countries. There is a column AffiliatedToOrganisation, which is a checkbox. I can check the box to store the knowledge that this country is affiliated with a particular organisation we are working with.
    Example data:
    TableCountries
    Country...AffiliatedToOrganisation
    Denmark..TRUE
    Norway....TRUE
    Sweden....FALSE
    Germany..TRUE

    I have a TableCases. In TableCases, there is a column named CaseNo, with a unique case number for each record. There is a column named CaseStatus where user may enter 1 or 0. There is a column Country where user may enter the information that a particular case concerns a particular country. We may have more cases concerning each country. The value in each field of the column Country in TableCases is therefore a Lookup related to TableCountries.

    Example data:
    TableCases:
    CaseNo...Status...Country
    1.............0...........Denmark
    2.............0...........Norway
    3.............1...........Norway
    4.............1...........Germany
    5.............1...........Sweden


    I have a QueryToDo containing all of the information.

    In QueryToDo I want for the case worker who is looking at the query to gather information about certain steps that must be taken. We need to send particular messages to these countries, based on status and on their affiliation.
    If status is 1, we need to look at the country's affiliation. If affiliation is also TRUE, case worker has to send a letter of type A. If affiliation is FALSE, case worker has to send a letter of type B.
    If status is 0, case worker does not need to do anything.

    Therefore, in the QueryToDo, I generate an IIf expression ToDo.
    ToDo: IIf(Status = 1; IIf(AffiliatedToOrganisation = TRUE;"You have to send a letter of type A to this country.";"You have to send a letter of type B to this country."); "You do not have to send a letter in this case.")

    Example data returned in QueryToDo:
    QueryToDo:
    CaseNo...Status...Country...AffiliatedToOrganisation...ToDo
    1.............0...........Denmark..TRUE........... .......................You do not have to send a letter in this case.
    2.............0...........Norway....TRUE.......... .........................You do not have to send a letter in this case.
    3.............1...........Norway....TRUE.......... .........................You have to send a letter of type A to this country.
    4.............1...........Germany..TRUE........... ........................You have to send a letter of type A to this country.
    5.............1...........Sweden....FALSE......... ........................You have to send a letter of type B to this country.

    This works.

    I am trying to improve in two ways. First, I soon reach the limit for how long the IIf expression can be, and that limits the amount of text I can provide to the case worker and the complexity of the IIf expression. This is a simple sample, the real query is way more complicated, but works in the same way, only with more options, more organisations, more criteria. I can do all of this, but the IIf expression becomes very long because of all the text. Second, my case workers would like the text formatted, so that for instance they can see the text "You do not have to send a letter in this case." in green, while the text "You have to send a letter of type A to this country." may be red and the text "You have to send a letter of type B to this country." may be blue. Or bold, or italics, or whatever works best for them.


    I believe a solution may be to store that text, and store it as rtf, in a table with a column for ID and a column with the needed feedback:
    TableFeedback
    FeedbackID...ProvideFeedback
    1....................You do not have to send a letter in this case.
    2....................You have to send a letter of type A to this country.
    3....................You have to send a letter of type B to this country.

    What I would like is for the IIf expression ToDo to retrieve the relevant text from ProvideFeedback, based on FeedbackID.


    Would this be a feasible way to go, and how to do it?

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Nanuaraq View Post
    I can check the box to store the knowledge that this country is affiliated with a particular organisation we are working with.
    From here follows, you can work with several organisations! And very probably some countries may be affiliated with several of those organisations! Both of those issues mean, you can't declare affiliation in TableCountries - you need for this a separate table like
    TableAffiliations: AfiliationID, Country, Organisation

    I assume you call the report from form button event? Wen yes, then no need for fancy IIF's in query. Simply check for conditions in button event, and run appropriate query/report. The easiest way will be calling separate reports for every set of conditions (this means you have to design a lot of them). More fancy way will be to design one or some general reports, create a query string according with set of conditions, and run the report with query string as parameter.

    PS! You can have max 7 nested iif's. You can cheat a bit with this, but not very much, and this makes query string much longer.

  5. #5
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77
    Quote Originally Posted by ArviLaanemets View Post
    From here follows, you can work with several organisations! And very probably some countries may be affiliated with several of those organisations! Both of those issues mean, you can't declare affiliation in TableCountries - you need for this a separate table like
    TableAffiliations: AfiliationID, Country, Organisation
    I am aware of this, indeed I need not only one more table but two; one table for the organisations and one table for the relations between each country and their respective organisations. Many-to-many. True. However, when working it out I realized that it was actually simpler - and a lot(!) easier to work with in the IIf statement - if I made a column named after the organisation in TableCountries and a checkbox. This is not 100% true to the normalization ideal, but in this context it is adequate, easier to work and actually less data to store. It probably couldn't be done this way in SQL, that's why I'm using Access.

    Quote Originally Posted by ArviLaanemets View Post
    I assume you call the report from form button event? Wen yes, then no need for fancy IIF's in query. Simply check for conditions in button event, and run appropriate query/report. The easiest way will be calling separate reports for every set of conditions (this means you have to design a lot of them). More fancy way will be to design one or some general reports, create a query string according with set of conditions, and run the report with query string as parameter.
    This sounds a lot more complicated than what I do now, and what I do now works. I see no need to incorporate this into form button event instead. Sounds like just another way of doing the same.

    Quote Originally Posted by ArviLaanemets View Post
    PS! You can have max 7 nested iif's. You can cheat a bit with this, but not very much, and this makes query string much longer.
    Yes I found out. I also found a nice workaround which is actually also much easier to comprehend for a human being.


    But... back to the question. How do I create a DLookup?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    It probably couldn't be done this way in SQL, that's why I'm using Access.
    I think wouldn't rather than couldn't. And I wouldn't recommend it as a way to do it in Access.

    Access uses SQL same as all the other RDBMS's - each of which have a lot in common but sometimes have additional functionality or slightly different ways of doing things.

    From your more detailed description it is clear you have adopted an Excel methodology for your table design. Excel uses a methodology far removed from Access and you will quickly find trying to force this methodology on Access (or any RDBMS) will quickly end in at worst an application that simply does not work or at best one that needs constant corrections and adjustments with enormous amounts of code and a really slow performance. Your current question in point - with a properly designed table structure your requirement would be a simple query, there would be no need for your iif statement, let alone addressing the issue of it's length.

    Access is not a larger Excel, so if you want to persist in this method, suggest perhaps you should use Excel for your project.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    From Post #3:
    Quote Originally Posted by Nanuaraq View Post
    <snip>The value in each field of the column Country in TableCases is therefore a Lookup related to TableCountries.<snip>
    It sounds like you are using Look Up FIELDS (in tables). This is a very bad idea! (see http://www.theaccessweb.com/lookupfields.htm)


    The formatting (text color, bold, italics) of the text could be done in a report control using Conditional Formatting. You would not to the formatting in the report query. (if I understand correctly what you were asking)

  8. #8
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77
    *sigh* I wish advice was something else than "Don't do that".
    Yes I read the link, no it doesn't apply to my situation.

    Forget about it, I'll start over, asking simply how to create a dlookup in design view. that should take care of my problem.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    how to create a dlookup in design view
    provided in my first response - if in a query, give it a field name, if a textbox control on a form, put it in the controlsource and precede it with an = sign

    good luck with your project

  10. #10
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77
    Quote Originally Posted by Ajax View Post
    I think wouldn't rather than couldn't. And I wouldn't recommend it as a way to do it in Access.

    Access uses SQL same as all the other RDBMS's - each of which have a lot in common but sometimes have additional functionality or slightly different ways of doing things.

    From your more detailed description it is clear you have adopted an Excel methodology for your table design. Excel uses a methodology far removed from Access and you will quickly find trying to force this methodology on Access (or any RDBMS) will quickly end in at worst an application that simply does not work or at best one that needs constant corrections and adjustments with enormous amounts of code and a really slow performance. Your current question in point - with a properly designed table structure your requirement would be a simple query, there would be no need for your iif statement, let alone addressing the issue of it's length.

    Access is not a larger Excel, so if you want to persist in this method, suggest perhaps you should use Excel for your project.
    Well, apart from the personal remarks about my thinking, would you mind telling how a properly designed table structure and simple query would solve it?

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Various suggestions have been and made requiring extra tables

  12. #12
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77
    What suggestions?

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe you would post your dB for analysis?

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

Similar Threads

  1. Replies: 13
    Last Post: 10-28-2017, 10:37 AM
  2. Replies: 3
    Last Post: 04-25-2016, 04:23 AM
  3. Replies: 5
    Last Post: 08-18-2015, 11:41 AM
  4. Replies: 1
    Last Post: 11-03-2014, 12:31 PM
  5. Replies: 1
    Last Post: 12-20-2010, 09:09 AM

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