Solved

Open report does not recognize number as filter ACCESS 2010 VBA

Posted on 2014-11-17
6
386 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Why can't I get my report to SORT correctly? 5 27
DSum for Access 6 43
Query to summarise data Like Pivot Table 3 29
access 7 20
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

911 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

21 Experts available now in Live!

Get 1:1 Help Now