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!!!!!!
LePrezAtWorkAsked:
Who is Participating?
 
shanesuebsahakarnConnect With a Mentor Commented:
Trap the error with a handler:

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#"       <------ Error occur here

  Me.Requery

Change_Exit:
  Exit Sub

Change_Error:
  If Err.Number=2771 Then
     MsgBox "No data!"
     Me.FilterOn=False
     Resume Change_Exit
  Else
    MsgBox "Error " & Err.Number & ": " & Err.Description
  End If
End Sub

BTW, you don't need the Requery line - just swap the FilterOn and Filter lines around.
0
 
MourdekaiCommented:
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) & "#"
0
 
LePrezAtWorkAuthor Commented:
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
0
 
LePrezAtWorkAuthor Commented:
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!!
0
 
shanesuebsahakarnCommented:
No problem, glad I managed to point you in the right direction.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.