Checkbox = True when current date is passed

[d-mcc56]

Hey All

I have a form that allows you to search for records and displays all the information for that project, typical stuff.

On this form is a subform with the followings Fields:
Inpection Requested, Inspection Due, Inspection Done, Overdue

When you update inspection requested the inspection due is automatically updated to 30 work days from that day.

Now what I'm trying to do is get the overdue field (it's a yes/no field) to automatically check itself when the current date is passed the inspection due and the inspection done field is blank.

Here's what I have that isn't working:

Private Sub Form_Current()
If DateAddW([REQUEST], 30) < Date And [INSPECT DONE] = 0 Then
[OVERDUE] = True
End If
End Sub

DateAddW is a UDF that works just fine. I've tried replacing 0 with Null and neither works. Any help is greatly appreciated

Thanks

 

[CJ_London]

there should not be a need to have this field since it can be part of your query. To include it will give you lots of maintenance issues

So you would expect your query to look something like

SELECT *
FROM myTable
WHERE Overdue=True

but you can achieve the same thing without that field by having your query look like this

SELECT *
FROM myTable
WHERE DateAddW([REQUEST], 30) < Date And [INSPECT DONE] = 0

 

[MarkK]

And it'll be more efficient--and clearer IMO--to just do the date addition without a function call, like . . .

WHERE [REQUEST] + 30 < Date And Not [INSPECT DONE]

 

[smig]

I totally agree about not saving this data into a field.

I don't like treat dates as simple numbers. I think there is a reason why they made the DateAdd() function.
I'll be happy to be proved I'm wrong and get rid of the DateAdd() function

 

[CJ_London]

I think there is a reason why they made the DateAdd()

You can treat dates as a number for days only. You need DateAdd if you want to add/subtract months/years, weeks, minutes seconds etc - unless you want to write your own algorythm to handle leap years, varying month lengths etc.

If the OP wanted to find for the next month, rather than 30 days, you would use DateAdd