Solved

Error 2771 with Date Picker

Posted on 2004-09-14
5
275 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Search for text in a .txt file 14 46
Any Way to Print an Import Spec? 3 32
Reference Controls on subforms 7 28
SQL Group on First occurrence 9 26
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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 how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

831 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