Solved

Error 2771 with Date Picker

Posted on 2004-09-14
5
285 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
[X]
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
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

691 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