• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

OpenForm with date range criteria VBA issue - Access 2003 with SQL back end

I have a form (Form A) where a user can enter and or select search criteria (including from and to dates) and hit a Search button to bring up another form (Form B) displaying results. The record source of Form B is a specific table. This is an Access application where the Access tables are linked to their counterparts in a SQL 2000 database. My issues is this: when a date range is the criteria, when I hit Search, I get the runtime error 2501 Open form action was canceled error message.

What is interesting is this: in the immediate window I can copy what is displayed by the Debug Print, and I can paste this into SQL Enterprise Manager SQL pane (after locating the table involved and displaying all rows). And when I run the query, I actually get results. What are your thoughts? Do you see issues with the code snippet? The code snippet is in the click event of the Search button of Form A.

stLinkCriteria = "[TransactionDate] >= '" & Me![BeginDate].Value & "' AND [TransactionDate] <= '" & Me![EndDate].Value & "'"    
stDocName = "PreEntry"
    Debug.Print stLinkCriteria
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Open in new window

1 Solution
Try the following (using #'s as delimiters):

stLinkCriteria = "[TransactionDate] >= #" & Me![BeginDate].Value & "# AND [TransactionDate] <= #" & Me![EndDate].Value & "#"
maxwell2323Author Commented:
That totally solved the issue. Thanks !!
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now