Results 1 to 5 of 5
  1. #1
    Bradcito is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2018
    Posts
    2

    Navy wide advancement exam query (tir listing)

    I'm the admin officer for a Navy schoolhouse hosting Navy wide exams. Our Sailors in certain ranks may only be eligible to take the exam if their time in rating (TIR) is less than a certain date. The year may be 2 or 1 year less than the current year.

    ‘March exam
    ‘Prior year



    If DatePart("m", date()) = 10, 11, 12
    Rank=E5 and datTIR <= 1 July, CY-2
    or
    Rank=E4 and datTIR <=1 July, CY
    or
    Rank=E3 and datTIR <= 1 January, CY+1

    ‘Current Year (March exam)
    If DatePart("m", date()) = 01, 02, 03
    Rank=E5 and datTIR <= 1 July, CY-3
    or
    Rank=E4 and datTIR <=1 July, CY-1
    or
    Rank=E3 and datTIR <= 1 January, CY

    ‘September exam
    If DatePart("m", date()) = 05, 06, 07, 08, 09
    Rank=E5 and datTIR <= 1 January, CY-2
    or
    Rank=E4 and datTIR <=1 January, CY
    or
    Rank=E3 and datTIR <=1 July, CY

    Will someone please give me some pointers? I will simply use the database to include all current students and students with future graduation dates.
    Last edited by Bradcito; 06-13-2018 at 07:39 PM. Reason: Query needed cleaning up

  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
    Not sure what the goal is, but the test has to repeat the first part, like:


    If DatePart("m", date()) = 10 Or DatePart("m", date()) = 11 Or DatePart("m", date()) = 12 Then

    id probably just use the Month() function rather than DatePart().
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Bradcito is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2018
    Posts
    2
    Thank you very much pbaldy! Yes, using the currently month function is the first part; the second part is to develop the query if a Sailor's time in rating (job) date in on or before a specified date...to take the September exams:
    E5 Sailors must have a TIR before 1 January 2016
    E4 on/before 1 January, current year.
    E3 on/before 1 July, current year.

    ...back on the first part: I know enough code to be more confused: Can VBA be written If month() like {10,11,12} ; since my query is in an active and future query?

  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
    Quote Originally Posted by Bradcito View Post
    ...back on the first part: I know enough code to be more confused: Can VBA be written If month() like {10,11,12} ; since my query is in an active and future query?
    You'd still need the Date() function. I'm lazy, so Month(Date()) seems simpler than DatePart("m", date()).

    If I'm following, things like this "
    datTIR <= 1 July, CY-3" could be done with DateSerial(): DateSerial(Year(Date()) - 3, 7, 1).

    If you're saying those tests get combined, you might use Select/Case on the month, and then further tests within that.

    Code:
      Select Case Month(Date)
        Case 10 - 12
          'code here for that
        Case 1 - 3
           'code here for that
        Case Else
           'code here for that
      End Select
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Gicu's Avatar
    Gicu is online now VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You don't really say how you intend to use this. Are you trying to build a list of sailors eligible for a certain exam date (say you select September 2018 in a text or combo box on a form and it lists all those eligible to take the exam in a subform or open a report), are you trying to show all sailors and calculate their first eligible exam date, etc. In a query you can use Month(Date()) IN (10,11,12), in VBA you would use Select Case/Case 10,11,12.

    Cheers,
    Vlad

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

Similar Threads

  1. Transformation from Wide to Tall
    By rigglesbee in forum Programming
    Replies: 5
    Last Post: 08-19-2014, 12:34 PM
  2. Navy PFA(PRT) 2011
    By BusDriver3 in forum Access
    Replies: 8
    Last Post: 08-16-2011, 06:57 AM
  3. Replies: 7
    Last Post: 12-29-2010, 04:07 PM
  4. Navy Help
    By dfelock in forum Database Design
    Replies: 9
    Last Post: 12-16-2010, 11:14 PM
  5. Navy IT Needs help
    By ITChevyDON in forum Queries
    Replies: 2
    Last Post: 02-02-2009, 06:51 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