asked on

Error 2771 with Date Picker

Hi,

I use a date picker control in an acces 97 form and i want to filter the form based on the date picked. I know that error occur because i don't have any data for a specific date. I don't have any idea to override this... can anyone help?

There is the form VBA :

Private Sub Form_Open(Cancel As Integer)

Me.FilterOn = True
Me.Filter = "SNMPR_DateCompleted >= #" & Format(Now(), "YYYY-MM-DD") & _
" 00:00:00# AND SNMPR_DateCompleted <= #" & Format(Now(), "YYYY-MM-DD") & _
" 23:59:59#"
DoCmd.Restore

End Sub

Private Sub CtlActiveX36_Change()

Me.FilterOn = True
Me.Filter = "SNMPR_DateCompleted >= #" & Format(CtlActiveX36.Value, "YYYY-MM-DD") & " 00:00:00# AND SNMPR_DateCompleted <= #" & Format(CtlActiveX36.Value, "YYYY-MM-DD") & " 23:59:59#" <------ Error occur here

Me.Requery

End Sub

Thanks!!!!!!

ASKER CERTIFIED SOLUTION

shanesuebsahakarn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mourdekai

Why not try something like this for your filter instead?

Me.Filter = "Fix(SNMPR_DateCompeted) = #" & Date() & "#"

and

Me.Filter = "Fix(SNMPR_DateCompeted) = #" & Fix(CtlActiveX36.Value) & "#"

LePrezAtWork

ASKER

Hi shanesuebsahakarn,

This is a good start point but it is not working the way I want. With this trap when I start from a day with data and selecting a day without data I got no message box and the form is filtered and no data is displayed... thats ok. But, if I start from a day without data and select a day with data I got the No data! MsgBox and the form is not filtered but I want it to display only the data for the chosen day. It's look like the error occur when I try to filter the form when he don't have any data at the start.

Any idea to a way to trap it the right way??

Thanks

LePrezAtWork

ASKER

Hi shanesuebsahakarn,

This look to work the way I want it to work :

Private Sub CtlActiveX36_Change()
On Error GoTo Change_Error:

Me.FilterOn = True
Me.Filter = "SNMPR_DateCompleted >= #" & Format(CtlActiveX36.Value, "YYYY-MM-DD") & " 00:00:00# AND SNMPR_DateCompleted <= #" & Format(CtlActiveX36.Value, "YYYY-MM-DD") & " 23:59:59#"

Change_Exit:
Exit Sub

Change_Error:
If err.Number = 2771 Then
Me.FilterOn = False
Me.Filter = "SNMPR_DateCompleted >= #" & Format(CtlActiveX36.Value, "YYYY-MM-DD") & " 00:00:00# AND SNMPR_DateCompleted <= #" & Format(CtlActiveX36.Value, "YYYY-MM-DD") & " 23:59:59#"
Me.FilterOn = True

Resume Change_Exit
Else
MsgBox "Error " & err.Number & ": " & err.Description
End If

End Sub

Thanks for your help!!

shanesuebsahakarn

No problem, glad I managed to point you in the right direction.