Lookup date value

[Eddyoftheyear]

I have a staff_table with field name testdate. Each staff must take this one test yearly to use him/her in any activity.

I have Activity_tbl that contain staffID, what I want is that if we select the staffID from combobox in the activity_tbl,then we enter the services date, a Dlookup looks the staff in the staff_tbl and see if he or she's test date is taken before the service date, if not msgbox pop up mentioning, you must take the test first! If the field is blank in the staff_tbl, then mesgbox, must take the test.

The field in the staff table: StaffID, Testdate
the fields in the activity_tbl is Activityservicedate

 

[randy700]

Something like this?

If Dlookup(#testdate#,"staff_tbl","staffID=" me.combobox) < #servicedate# Then
    Msgbox "You must take the test"
End If

Randy

 

[Eddyoftheyear]

What I did is that I brought the field to the form and used the following codes. It works nicly but if the testdate field is blank, the msgbox should appear.


Dim oShell As Object
Dim iResponse As Integer
Set oShell = CreateObject("Wscript.Shell")
If Me.testdate= "" Then
If Me.servicedate>= Me.testdate+ 365 Then
MsgBox "You must take the test."

iResponse = MsgBox("Would you like to see link for access to this Test?", _
vbYesNo, "Access Denied")

If iResponse = vbYes Then
oShell.Run ("

http://www.google.com")

Else
Exit Sub
End If
End If
End If

Any suggestions!

 

[randy700]

So, simply add it.

If Me.testdate= "" Then
[red]MsgBox "You must take the test.".[/red]
If Me.servicedate>= Me.testdate+ 365 Then
MsgBox "You must take the test."

Randy