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

Posted on 2008-11-07
Last Modified: 2013-11-28
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

Question by:maxwell2323
    LVL 61

    Accepted Solution

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

    stLinkCriteria = "[TransactionDate] >= #" & Me![BeginDate].Value & "# AND [TransactionDate] <= #" & Me![EndDate].Value & "#"

    Author Closing Comment

    That totally solved the issue. Thanks !!

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    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…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now