Query Code to Hi-light Date Triggers

[sbrem00]

Hi,



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.

Thanks

Sam

[Robeen]

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.
 
Exit_Get_DB_Values:
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Set db = Nothing
    Exit Function
 
Error_Handle:
    Resume Exit_Get_DB_Values
End With
End Function