Solved

Error 2771 with Date Picker

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

696 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