Results 1 to 8 of 8
  1. #1
    pjdube is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    May 2017
    Posts
    25

    Getting Two Field Values in Text Field on Report

    Dear All,



    Again, thanks for your earlier help.

    I have a report which has students progress. The progress is measured by the Section they are on and the Total Sections for the Course.

    The two fields I have is [Section] and [Sections] on the report which I am trying to make show, I conquered the first part.

    I have laid out my report as a progress report and I have textboxes for each Section, like this:

    Student Course 1 2 3 4 5 6 7 8
    John Jones Blah Blah Course X X X
    Hillary Main Blah Blah Blah Course X X X X X X


    I figured out how to fill out the individual cells with "X" if they are done with that section.

    Here is my Control Source for one of these textboxes (so you get an idea):

    Code:
    =IIf([Sec]>=13,"X","")
    This code is in the 13th textbox Control Source.

    Now here is my problem, which I am sure can easily be figured out, how do I put a "E" in
    the textbox if it is at the end of the Sections (i.e. the last section of the course).

    I tried finding out how to nest the IIF statement, but got nowhere. Then I tried "OR" in it but that
    doesn't work.

    Basically what I want to do is as follows:

    Code:
    =IIf([Sec]>=13,"X","") AND IIf([Sec]=[Secs],"E","")
    But that doesn't work. By the way "Sec" is "Section" and "Secs" is Total Sections in the Course.

    Any pointers?

    Thanks!

    Phil

  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
    Try

    =IIf([Sec]>=13,"X",IIf([Sec]=[Secs],"E",""))
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pjdube is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    May 2017
    Posts
    25
    Hey thanks pbaldy,

    I tried that, and that seems to help a little, however when I used that the "E" only shows up when the student finishes the last lesson, however I am trying to have the boxes "disappear" (after the last lesson of the course) by using the conditional formatting to only show the textbox up to the end of the lesson for the course - so I can easily see the progress.

    For instance:

    HTML Code:
                                                              End of Course
                                                                          |
                                  1    2    3   4    5   6  |  7    8    9
    Brian Jones   Blah Course  [X] [X] [X] [X] [ ] [ ] | [E] [E] [E]  <----Need these to "disappear"
                                                                    |
                                                                    |____
                                                                            |
    Blake Smart  Bleg Course  [X] [X] [X] [X] [X] [ ] [ ] [ ]|[E] [E] <----Need these to "disappear"
                                                                            | 
    Everything with "E" will "disappear" using the Conditional Formatting by making the textbox White and the text White.

    That's why I was trying to see if there could be an OR operator in the syntax to have it do TWO IIfs.

    I hope the above helps clarify. I have uploaded an image of the report.
    Click image for larger version. 

Name:	REPORT.png 
Views:	18 
Size:	44.4 KB 
ID:	41810
    Thanks!!!

    Phil
    Last edited by pjdube; 05-10-2020 at 10:18 PM. Reason: Including image

  4. #4
    pjdube is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    May 2017
    Posts
    25

    Attached Sample of the Database

    Attached is a sample database of what I am trying to handle as mentioned above.

    Hope this helps!

    Phil


    StudentProgress.zip

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm about to hit the road, but you could probably hide it with the formula:

    =IIf([Sec]>=32,"X",IIf(32>[secs],"",IIf([Sec]=[Secs],"E","")))
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    pjdube is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    May 2017
    Posts
    25
    Paul,

    Thanks! I tried that and unfortunately this did not work.

    One question, should I just go with VBA?

    I looked on-line and found some coding that might do the trick.

    So far I have developed the below but it seems to not be doing anything at all in my report, and I was hoping that you could help me out here:

    Code:
    Private Sub Report_Current()
    
    
    For i = 1 To 80
         If Me.Controls("L" & i) > Me.Secs Then Me.Controls("L" & i).Visible = False
    Next i
    
    
    End Sub
    I named each of the textboxes "L" with the sequential numbers. I.e. "L1", L2" etc. "L" stands for Lesson and the number is the the number of the lesson. The total Lessons is 80.

    However as I have mentioned before certain courses only have 21 lessons, and some have 76, however if I do > Secs then I was hoping it would blank out those after the last section.

    With hope,

    Phil

  7. #7
    pjdube is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    May 2017
    Posts
    25
    I figured it out! I just assigned each textbox with the value "=1", "=2" in the Control Source (all the way to 80), and then used Conditional Formatting and figured out how to show how many sections there were in the course and which ones they finished.

    Thanks for your help though. I am going to learn more about the IIf function so that I fully understand it and am able to use it in my other access forms.

    Phil

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it sorted Phil!
    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. Replies: 3
    Last Post: 11-04-2019, 06:52 PM
  2. Replies: 13
    Last Post: 06-08-2019, 06:09 PM
  3. Replies: 18
    Last Post: 03-26-2015, 07:26 AM
  4. Replies: 1
    Last Post: 05-24-2013, 02:44 AM
  5. Hve text combo box values but store integers in field
    By accesshelpasker in forum Access
    Replies: 9
    Last Post: 10-28-2011, 10:49 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