Solved

Open report does not recognize number as filter ACCESS 2010 VBA

Posted on 2014-11-17
6
378 Views
Last Modified: 2014-11-17
I have a procedure that worked when using the "=", but I changed to "IN".  However, I get a runtime errr orf 3075, Syntax error (missing operator) inquery expression ' DealID IN(29,16) Order by UpdateDate Asc'.  The statement looks fine in the debug window

DealID IN(29, 16) Order by UpdatedDate ASC

It looks fine so am not sure why it does not work when passing the filter in the open report statement.

Sandra
0
Comment
Question by:ssmith94015
  • 3
  • 2
6 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 40448087
When you have this type of question (syntax) it is impossible for us to do anything but guess when you don't post the actual query/code.

I'm going to guess that you are trying to supply the In() as a parameter and you can't do that.  If you want to use In(...) in a query, you will need to build the entire SQL string with VBA and run that.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40448109
You can't have "DealID IN(29, 16) Order by UpdatedDate ASC" as a filter.
You can have "DealID IN (29, 16)"
Specify the ordering in Grouping and Sorting of the report. not (and never) in the source query.

/gustav
0
 

Author Comment

by:ssmith94015
ID: 40448110
Hi Pat

Actually, once I got rid of the Order By Clause it works in.  So perhaps the real question is in code, how do I sort the report dynamically?

Sandra
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:ssmith94015
ID: 40448113
The sort order can be selected by the user based on various date categories in the filter form.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40448126
If the report order is not dynamic, use the report's sorting and grouping properties to specify sort order.  Never do it in your query.  Access always rewrites the report's RecordSource query to eliminate columns it thinks you don't need and it ignores your sort order also.
0
 

Author Closing Comment

by:ssmith94015
ID: 40448139
Since this actually answered the question I asked, I am accepting it.  Am posting another question to resolve the dynamic sort problem.

Sandra
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

746 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

17 Experts available now in Live!

Get 1:1 Help Now