Results 1 to 11 of 11
  1. #1
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48

    Store an IIF option is a table field

    Hi

    I am a total newbie to this forum and Access. Please excuse me if this is the wrong forum to post this question.

    I have a field called [Status] in a form that uses an IIF statement that throws out a result based on a date in a previous field. It displays either "Completed" "Delayed" or " In Progress" depending on the date selected.

    I want to store the result i.e. "Completed" "Delayed" or " In Progress" in the table field [Status]

    1. Is this the best way to do it?
    2.If it can be done via the form, how do I do it.

    Any assistance would be greatly appreciated.

    Thank You

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    No, that isn't the best way to do it.
    While newer versions of Access allow you to store some calculated fields in Access, it is not a good idea. It violates the rules of database normalization, and is not compatible with other database programs (like SQL).

    Anything that can be calculated "on-the-fly" should NOT be stored at the Table level, it should be calculated in a Query. And you can use Queries as the basis of Reports, Forms, and Exports, just as easily as you can with Tables.

  3. #3
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48
    =IIf([ActualCompDt] Is Not Null,"Completed",IIf([PlanStartDt] Is Null,"Not Scheduled",IIf([PlanCompDt]<Date(),"Delayed",IIf([PlanCompDt]>=Date(),"In Progress"))))


    That is the statement in the field on the form. I dont know if that is the most efficient way of doing it.

    How would I incorporate this into a query ?

    The end game that is required is that I am able to flag all the "delayed" results and then as in Excel filter it out.

    I have done this quite easily is Excel - but now I have to do it in Access.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    It is quite easy in Access too, once you know how to do it.
    See here: http://www.fontstuff.com/access/acctut02.htm

    Note that you already have the formula you need. You just need to place it in a calculated field in Access.
    And then, on the Criteria row under this field in your query, to filter out all "Delayed" results, just place this on that row:
    Code:
    <> "Delayed"

  5. #5
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48
    Thank You !!

    Will test and report back.

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome!

  7. #7
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48
    Hi There. Got it to work


    Thank You So Much !!

    I am now trying to add another created field which calculates the time the task was delayed

    I changed the criteria from <> Delayed to <> completed

    So now it shows me the completed tasks

    So once a completion date is entered in the form it calculates by how many days the task was delayed if any

    =IIf([status]="completed",[actualcompdt]-[plancompdt],"")

    I want this now also added to the query, this is what i have done:

    DaysDelayed:IIf([status]="completed",[actualcompdt]-[plancompdt],"")

    Criteria [actualcompdt]-[plancompdt]

    The query runs but returns an empty table... what am i doing incorrectly?

    Thanks

    ============
    Update
    ============


    DaysDelayed:IIf([status]="completed",[actualcompdt]-[plancompdt],"") - I Changed that field name to the first "created" field by the query. It runs now but comes up asking for a parameter to be filled - which is in this case "completed"

    How do I get it to do that automatically ?

    Thanks

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    First, thing. Access does not like mixed data types. Since your Days Delayed is a numeric field, I would recommend having it return 0 instead of "", i.e.
    Code:
    DaysDelayed:IIf([status]="completed",[actualcompdt]-[plancompdt],0)
    I am not quite sure I completely follow what you are saying about your latest query. Can you post the SQL code of that query here?
    All you need to do is switch it to SQL View, and copy and paste the SQL code here.

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    Let's return to your calculated Status field.

    You have a record, where you entered p.e some date into field PlanCompDt, and calculated Status = "In Progress" and stored Status for this record. Now you never edit this record anymore. At some time the value for PlanCompDt will be less than current date, but the status remains "In Progress", and is not changed to "Delayed" as it should be. And when you run p.e. a report based on your table, you get wrong status in report!

    To avoid such problems, calculate the status in AfterUpdate events for date controls and write the result into form control linked to status field (an ordinary database field). Additionally, you need a procedure to recalculate the statuses for whole table at OnOpen or OnClose event of form (or at both of them to be on safer side), so all statuses for which some time limit is reached will be recalculated even when you don't edit according records in your working session.

    It will be easier when you have SQL database as back-end. There you can define Job to run on shedule (p.e. at every morning before workday begins) and execute stored procedures which are then making such updates automatically.

  10. #10
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48
    Hi Guys

    Thank you for your replies. I appreciate it.

    Although SQL would be the perfect solution, its not an available option right now.

    I have attached cropped screen grabs of what is occurring.

    SQL
    ====
    SELECT DateCalc.PlanStartDt, DateCalc.PlanCompDt, DateCalc.ActualCompDt, DateCalc.ID, DateCalc.Status, DateCalc.Flag, IIf([ActualCompDt] Is Not Null,"Completed",IIf([PlanStartDt] Is Null,"Not Scheduled",IIf([PlanCompDt]<Date(),"Delayed",IIf([PlanCompDt]>=Date(),"In Progress")))) AS Planned, IIf([planned]="completed",[actualcompdt]-[plancompdt],"") AS Overdue
    FROM DateCalc
    WHERE (((IIf([ActualCompDt] Is Not Null,"Completed",IIf([PlanStartDt] Is Null,"Not Scheduled",IIf([PlanCompDt]<Date(),"Delayed",IIf([PlanCompDt]>=Date(),"In Progress")))))="completed") AND ((IIf([planned]="completed",[actualcompdt]-[plancompdt],""))=[actualcompdt]-[plancompdt]));

    =======

    I am satisfied with the way its working - all I need (as seen in the attached file) is to get rid of / automate the filling in of the "Enter Parameter Value" pop up box.

    Guys - Im a newbie and understand that there is probably a more efficient way of doing this in ACCESS, so any suggestions/advice will be more than welcome.

    -- "" updated to 0 in statement

    Thank You Query_Issue.pdf


    =========
    UPDATE
    =========

    I ran a query on a query and that has worked perfectly for me. Is this the wisest way to do it?
    Last edited by archie1_za; 11-01-2017 at 03:17 AM.

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I ran a query on a query and that has worked perfectly for me. Is this the wisest way to do it?
    That is fine. I use this technique a lot. Sometimes you can try to do too much in one query, and Access can choke on it.
    What I often do is if I have queries that do calculations, and then I want to use some of those calculations in my criteria, is I will have one query that does the calculations, but has no criteria. Then I will do a query of that query where I add my calculations.

    If you had wanted to do it all in one query, the thing to remember is that Access does not like to use Aliases of calculated fields in the Criteria section of the same query. So, if you substitute the "planned" alias in your query with the actual calculation, it will work. So that query would look like this:
    Code:
    SELECT 
        DateCalc.PlanStartDt, 
        DateCalc.PlanCompDt, 
        DateCalc.ActualCompDt, 
        DateCalc.ID, 
        DateCalc.Status, 
        DateCalc.Flag, 
        IIf([ActualCompDt] Is Not Null,"Completed",IIf([PlanStartDt] Is Null,"Not Scheduled",IIf([PlanCompDt]<Date(),"Delayed",IIf([PlanCompDt]>=Date(),"In Progress")))) AS Planned, 
        IIf([planned]="completed",[actualcompdt]-[plancompdt],"") AS Overdue
    FROM 
        DateCalc
    WHERE 
        (((IIf([ActualCompDt] Is Not Null,"Completed",IIf([PlanStartDt] Is Null,"Not Scheduled",IIf([PlanCompDt]<Date(),"Delayed",IIf([PlanCompDt]>=Date(),"In Progress")))))="completed") 
    AND 
        ((IIf(IIf([ActualCompDt] Is Not Null,"Completed",IIf([PlanStartDt] Is Null,"Not Scheduled",IIf([PlanCompDt]<Date(),"Delayed",IIf([PlanCompDt]>=Date(),"In Progress")))) ="completed",[actualcompdt]-[plancompdt],""))=[actualcompdt]-[plancompdt]));

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

Similar Threads

  1. Replies: 2
    Last Post: 04-19-2017, 08:09 AM
  2. Replies: 11
    Last Post: 11-01-2016, 03:58 AM
  3. Replies: 4
    Last Post: 12-10-2013, 03:55 AM
  4. Replies: 7
    Last Post: 11-03-2012, 06:17 PM
  5. Replies: 0
    Last Post: 09-25-2006, 03:42 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