Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Error 2771 with Date Picker

Posted on 2004-09-14
5
278 Views
Last Modified: 2008-03-03
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!!!!!!
0
Comment
Question by:LePrezAtWork
  • 2
  • 2
5 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 125 total points
ID: 12059916
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
 
LVL 8

Expert Comment

by:Mourdekai
ID: 12060174
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
 

Author Comment

by:LePrezAtWork
ID: 12064062
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
 

Author Comment

by:LePrezAtWork
ID: 12064619
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12064655
No problem, glad I managed to point you in the right direction.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question