Filter form based on DateDiff statement

[vad77]

The form I created has a field called "expire" which is a date field. I created a button to insert the current date. To the right of the expire filed is a textbox that has a DateDiff statement to count the number of days from the expire date.
=DateDiff("d",[expire],Now())
This all works great, my next task is to be able to filter any records that has a expire date of 30 days or greater. I have tried several different code on a command button with different results and never anything close to what I need filtered.
My statement eaither set a filter that still shows all records and another statement that filters down to 1 blank record.
I have tried the following...

Private Sub cmd_grtthirty_Click()
Me.Filter = Text88 > 29
Me.FilterOn = True
End Sub

and

Private Sub cmd_grtthirty_Click()
Me.Filter = DateDiff("d", Now, Expire) > 29
Me.FilterOn = True
End Sub

I need to filter on this criteria so I can delete these records. This will be easier to locate records instead of looking for records which match contional format.
Any help with this will be greatly appriciated.

[ItsMe]

These are untested but maybe you can incorporate some of this to your form. Not sure what the field name is. I used expire in this example.

Dim dtWarn as Date
dtWarn = Date + 30 'Determine the date for warnings
Dim strFilter as String
strFilter = "[expire] >= #" & dtWarn & "#"

Me.filterOn = False
Me.Filter = ""
Me.Filter = strFilter
Me.filterOn = True

This example should filter expirations between now and 30 days. I normally adjust the form's recordset but I believe between will work on the .Filter property.

Dim strShow As String
Dim dtWarn As Date
Dim dtToday As Date
Dim strFilter as String

    dtWarn = Date + 30 'Determine the date for warnings
    dtToday = Date

strFilter = " Between " & "#" & dtToday & "#" & " And " & "#" & dtWarn & "#"

Me.filterOn = False
Me.Filter = ""
Me.Filter = strFilter
Me.filterOn = True

Maybe you could combine the two and use a toggle button to switch between the two filters.

[vad77]

I tried both and they did not work. I left out a piece of information not all my records will have a date in the "expire" field only the records we are which we specify to watch when we enter a date. The rest of the records wll have a Null value. This database was built off of an excel spreadsheet. When a record was in question for renewal they highlighted in red. Then when it was time to clean up these records the filtered on the cell colr, red. Then they just deleted the row. In moving to Access I figured I put an field called "expire". If a record is to be watched for deletion the command button would enter the current date to start the countdown to deletion. I then added an unbound text box with a DateDiff statement to start counting the days from the expire date.
What I want to do is just on any record that has an expire date of 30 days or more.

[ItsMe]

maybe some where criteria then

strFilter = "[expire] >= #" & dtWarn & "# WHERE not ([expire]) is null"

Try that. I think I got the parentheses correct.

[vad77]

@ItsMe, thanks for taking the time to respond to my post.
I am thiking I am making this to compicated than it needs to be. I think just applying a filter on any record that has a date in the expire field would be better. I know I can just navigate to a record where the expire field is null and use the filter by non-blank
I think having a button on the form would be easier.

[ItsMe]

yah, I am not focusing on the big picture. I am offering filter options based on your request and experiences I have had in the past.

Let us know if you need further help. I don't think I see a question in post #5

[vad77]

OK, thanks!

[vad77]

Just an update...
This what I did to be able to filter on any record with a date. It might not be pretty but it gives the result I was looking for. I added a true/false field to my contractor table. Then I modified my command but to set that value to True and add the current date in the field named "expire". Next I added a command button that applies a filter on the field "marked" where the value is set to True.
The trouble I had was trying to apply a filter on a field that has different dates or a Null value.

[ItsMe]

I use Booleans to distinguish the status of records. One thing to remember about Booleans is they are not the smallest data type in the tool box.