Results 1 to 2 of 2
  1. #1
    sbrem00 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011

    Query Code to Hi-light Date Triggers


    I am working on a Staff Sickness database and am quite lost. I have a form with data about staff in it i then have a sub form within that where you can enter the sickness dates for staff.

    In the query for the sub form i would like to enter a code that will look at the start date of the sickness and if there has been equal to or more than 3 separate occasions of sickness within 3 months i would like the value to be 1. I would also like to add in that if there has been more that 6 separate days within 6 months then the value is 2.

    (Note: the first trigger is occations i.e. a count of entries and the second days i.e. the sum of the days)

    Pleas can someone help as i am not sure where to start.



  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Tulsa, Oklahoma.
    You could try using a function something like this:

    I just have a generic Select * SQL statement in there.
    You'll have to get your SQL statement to return the totals you need.

    If you're not familliar with how to do this, a good start would be to create queres in Query Designer that give you the information you need about a Staff member.

    Next - you would need to put those queries into your SQL String in the function.

    Next - you will have to provide your SQL strings with the values [Staff member ID, Date Range . . .] for the Criteria part of the strings.

    Finally, your code will take the values that your queries return and decide whether to put a 1 or a 2 on your Form.

    If you have not done this before, it might seem a little intimidating but you will get plenty of help here.

    All the best!

    Function Get_DB_Values()
    'Assumes that you have a Table1 and that Field1 is Text and Field2 is a Number.
    ' . . . You should use your actual Table & field names & set your variables
    ' . . . to match the data types of your table fields.
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strField1 As String
    Dim intField2 as Integer
    On Error GoTo Error_Handle
    Set db = CurrentDb
    strSQL = "Select * From [Table1]"
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    With rs
    'This Do While loop goes through all the records in strSQL.
    Do While Not rs.EOF
    'This is where you 'read' the data that your query returns.
    strField1 = rs![Field1]
    intField2 = rs![Field2]
    'I put this little If Then here so you can look at one value.
    'substitute these with your own values.
    'This is where you can process the values that your query gives you
    ' . . . and determine what to display on the Form.
    If intField2 = 5 Then
        'MsgBox strBranch
    End If
    .MoveNext             'Move to next record in recordset.
    Loop                  'Back to 'Do While' to check if we are at the end of the file.
        If Not rs Is Nothing Then
            Set rs = Nothing
        End If
        Set db = Nothing
        Exit Function
        Resume Exit_Get_DB_Values
    End With
    End Function

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

Similar Threads

  1. Code to combine report filter and date range
    By rhubarb in forum Reports
    Replies: 17
    Last Post: 10-28-2011, 03:08 PM
  2. query to update a date based on another date
    By tkendell in forum Access
    Replies: 3
    Last Post: 06-29-2011, 09:32 PM
  3. Count Down and close on date code
    By ibraomer in forum Programming
    Replies: 1
    Last Post: 01-03-2011, 08:58 PM
  4. Replies: 2
    Last Post: 08-17-2010, 01:10 PM
  5. Null Date control code problem
    By DanW in forum Forms
    Replies: 2
    Last Post: 11-10-2009, 03:13 PM

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 - Senior Forums