Hey,
I have a cmd button on my form that has this code attached to it:
Forms!frmClients.Form.Filter = "[Date Last Accessed] <#" & DateAdd("m", -1, Date) & "#"
Forms!frmClients.FilterOn = True
Now if it does not find any matching records, it will display a blank form. Is there a way to test if no records where found and if not turn off the filter?

Thanks

Maybe something like this:

**Air C0de** Untested

Forms!frmClients.Form.Filter = "[Date Last Accessed] <#" & DateAdd("m", -1, Date) & "#"
Forms!frmClients.FilterOn = True

If Forms!frmClients.Form.RecordCount < 1 Then
Forms!frmClients.Form.Filter = ""
Forms!frmClients.FilterOn = False
End If

I tested the code and it didnt work, i get an error msg.
Any other way where i can test if a record was found or not?

I tested the code and it didnt work, i get an error msg.
Any other way where i can test if a record was found or not?

I may have not had the correct syntax.

What is the error message?

Without knowing the error message and seeing the exact VBA code used, I am not sure what the problem may be.


What is the VBA code used? Please post all the code of the event, including the event name.

Maybe something like this:

**Air C0de** Untested

Forms!frmClients.Form.Filter = "[Date Last Accessed] <#" & DateAdd("m", -1, Date) & "#"
Forms!frmClients.FilterOn = True

If Forms!frmClients.Form.RecordCount < 1 Then
Forms!frmClients.Form.Filter = ""
Forms!frmClients.FilterOn = False
End If




maybe more like this



Forms!frmClients.Form.Filter = "[Date Last Accessed] <#" & DateAdd("m", -1, Date) & "#"

If Forms!frmClients.Form.RecordCount < 1 Then ' no records, don't filter
Forms!frmClients.FilterOn = False
Else ' there are records, turn on filter
Forms!frmClients.FilterOn = True
End If

I may have not had the correct syntax.

What is the error message?

Without knowing the error message and seeing the exact VBA code used, I am not sure what the problem may be.


What is the VBA code used? Please post all the code of the event, including the event name.
Hey Sorry i forgot to post the error.
Here is the code i used:
Forms!frmClients.Form.Filter = "[Date Last Accessed] <#" & DateAdd("m", -1, Date) & "#"
Forms!frmClients.FilterOn = True

If Forms!frmClients.Form.RecordCount < 1 Then
Forms!frmClients.Form.Filter = ""
Forms!frmClients.FilterOn = False
End IfI thought it gave me a syntax error, but it is the same error that wiklendt's code shows (See below)
maybe more like this



Forms!frmClients.Form.Filter = "[Date Last Accessed] <#" & DateAdd("m", -1, Date) & "#"

If Forms!frmClients.Form.RecordCount < 1 Then ' no records, don't filter
Forms!frmClients.FilterOn = False
Else ' there are records, turn on filter
Forms!frmClients.FilterOn = True
End If

I tried using that code but get a "RunTime error '2465', Application defined or object defined error"
This error comes up after testing the first line of the if statement.

Is there any other way i can filter a form and test for no records found? Maybe with queries?

[quote=rotteneggz;820866]"[Date Last Accessed] <#" & DateAdd("m", -1, Date) & "#"[/code]

hmm, yes. not sure how the dateadd would work in this context. the "#" hash is normally used for dates (it's the only way SQL seems to accept them) but i don't know if the ("m",-1,Date) changes this to a non-date value.

try changing the DateAdd() to just some known date and see if that works. e.g.:


strDate = Date()

"[Date Last Accessed] <#" & strDate & "#"