Results 1 to 5 of 5
  1. #1
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202

    Question Help with an Iif statement please

    I am working on a text box that will check if ALL of the field values for a particular EmployeeID are the same, if they are display one status message, if they are not, display a different status message.

    It's an employee tracking database for our companies safety training program. Each employee has many different types of training. I would like this text box to look and see if all of the field values [CompletionStatus] of a certain EmployeeID are "Complete". If this is true, then display "Training Complete", if not display "Training Incomplete".

    I am not sure which function I need to use to do this.....



    Here's what I am thinking:
    =IIf(DLookup("CompletionStatus","tblTrainingTypes" ,"EmployeeID = " & Forms![frmEmployees]!EmployeeID)="Complete","Training Complete","Training Incomplete")

    How far off am I? lol The statement above I would assume will only check the first record and not all of them, is there a different function that I should be using?

  2. #2
    kennejd is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    188
    I would take a different approach....create a query that counts the training that's been complete. Add a textbox and a dlookup.

    I've attached my example.

    The problem with this approach is if the training is recurring....will you need a due date?

  3. #3
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Hrmmmm, ok I understand about using a query to do this. My question with your example though that I don't understand is that you have it set to count the records that are marked as complete.

    When I go to do the DLookUp on the query, what would I be basing the lookup on that would tell it whether or not ALL of the records are complete?

    Bear with me here, I'm still learning aboot this shtuff.

    Edit: Ok wait how bout this... I could set th TCount to cout Incomplete records, and then if the dlookup returns a value greater than 0 the status would be "Training Incomplete". Would that work??

    Sorry my keyboard is running low on batts so some keys are not transmitting.

  4. #4
    kennejd is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    188
    The most important part is knowing how many records are needed to be considered 'complete'. I believe this comes from the TrainingTypeNames table...and at the moment there are 4 records and I'm assuming they're all required.

    However, since training requirements can be added and subtracted I changed my iif statement on the form to this:=IIf(DLookUp("tcount","qtcomplete")=DCount("T rainingTypeNameId","tblTrainingTypeNames"),"Comple te","Incomplete")

    If every employee isn't required every training event, then the structure of the database will need to be changed. Same thing if the training is recurring.

    Yes, you could look for 'incomplete' training as well....whichever way you prefer...

  5. #5
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Thanks for the help kennejd!

    I have decided to go with counting incomplete training. This way I can have the DLookUp just check for anything >0 to determine the result.

    The reason I went this route is because not all employees have the same training curriculum, and the training events that I currently have in the DB are just for testing purposes.

    Thanks again for your help, this will definitely work!

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

Similar Threads

  1. Need Help with SQL Statement
    By Hank44 in forum Programming
    Replies: 3
    Last Post: 11-08-2010, 05:49 PM
  2. SQL like statement?
    By Cojack in forum Queries
    Replies: 4
    Last Post: 09-21-2010, 04:45 AM
  3. If Then Statement Help
    By Kapelluschsa in forum Programming
    Replies: 5
    Last Post: 08-11-2010, 09:24 AM
  4. IIf Statement...HELP!
    By bdhFS in forum Queries
    Replies: 5
    Last Post: 05-19-2010, 07:55 AM
  5. IIF statement
    By james1982 in forum Access
    Replies: 1
    Last Post: 07-20-2009, 09:38 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