trying to show a date record in a form

[andytheit]

I have a database (Access 2003) of vehicles and i need to query when an MOT is due. I Wanted it to open a form and show the MOT date 10 days before the MOT is due. This is what i have so far. All this does is open the form and display th msgbox even when there are mot's due.

I would be very grateful of any help.

Thanks

Andy


Private Sub Command0_Click()
Dim vehicle As Database
Set vehicle = CurrentDb
Dim MOT As Date
MOT = Date + 10

DoCmd.OpenForm "FMotDueDate"
If Forms!FMotDueDate!MOTDate = MOT Then
Forms!FMotDueDate!MOTDate.SetFocus
Else: MsgBox "NO MOT'S ARE DUE", vbOKOnly

End If
End Sub

 

[Remou]

You have "= MOT", so it will only work when there is exactly 10 days difference. If you mean it to show any MOT that will come up in the next 10 days you would need something like:

[tt]If Forms!FMotDueDate!MOTDate >= Date And Forms!FMotDueDate!MOTDate < MOT Then[/tt]

Or perhaps you have a boolean completed control:

[tt]If Forms!FMotDueDate!MOTComplete = False And Forms!FMotDueDate!MOTDate < MOT Then[/tt]