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

Posted on 2008-11-07
Medium Priority
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

mbizup earned 500 total points
ID: 22906328
Try the following (using #'s as delimiters):

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

Author Closing Comment

ID: 31514426
That totally solved the issue. Thanks !!

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 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