Solved

Open report does not recognize number as filter ACCESS 2010 VBA

Posted on 2014-11-17
6
397 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 38

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 50

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 38

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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Make the most of your online learning experience.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Six Sigma Control Plans

717 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